Обсуждение: Constraint to ensure value does NOT exist in another table?
I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! Mike
On Thu, Jun 16, 2011 at 6:04 AM, Mike Christensen <mike@kitchenpc.com> wrote: > I know I can setup a FK constraint to make sure Table1.ColA exists in > Table2.Key, however what if I want to do the reverse? > > I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do > this with any sort of CHECK constraint, trigger, custom function, etc? The most common constraints are provided for, but then after that you have to use triggers. PostgreSQL deliberately doesn't support queries in CHECK constraints for this reason. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
>> I know I can setup a FK constraint to make sure Table1.ColA exists in >> Table2.Key, however what if I want to do the reverse? >> >> I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do >> this with any sort of CHECK constraint, trigger, custom function, etc? > > > The most common constraints are provided for, but then after that you > have to use triggers. > > PostgreSQL deliberately doesn't support queries in CHECK constraints > for this reason. Thanks! I wrote a Trigger for this and it seems to work fairly well.. I kinda figured that was the obvious way, but thought there might be some new 9.x feature that made this sort of thing possible.
-----Original Message----- From: Mike Christensen [mailto:mike@kitchenpc.com] Sent: Thursday, June 16, 2011 1:05 AM To: pgsql-general@postgresql.org Subject: Constraint to ensure value does NOT exist in another table? I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! Mike Trigger (with corresponding trigger function) will definitely do the job. Regards, Igor Neyman
I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a storedprocedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored proceduresqueries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on(constraint violated or not). I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is queryingis a ref table that is very static. This approach may not be bullet proof for checking key relationships in dynamictables. I'll let others speak to that. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Igor Neyman Sent: Thursday, June 16, 2011 9:21 AM To: Mike Christensen; pgsql-general@postgresql.org Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table? -----Original Message----- From: Mike Christensen [mailto:mike@kitchenpc.com] Sent: Thursday, June 16, 2011 1:05 AM To: pgsql-general@postgresql.org Subject: Constraint to ensure value does NOT exist in another table? I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! Mike Trigger (with corresponding trigger function) will definitely do the job. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 16 June 2011 14:41, Gauthier, Dave <dave.gauthier@intel.com> wrote: > I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a storedprocedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored proceduresqueries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on(constraint violated or not). > > I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is queryingis a ref table that is very static. This approach may not be bullet proof for checking key relationships in dynamictables. I'll let others speak to that. Did you use explicit locking? If not, you likely have a race condition. The same applies to any sort of enforcement of business rules inside triggers (or, indeed, check constraints). Check constraints are generally intended to enforce simple, immutable rules (i.e. that only reference the tuple that the rule is enforced on). I would have used a trigger instead. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
I preferred the check constraint mainly because of the feedback "violation" messages. Other, more traditional constraintsexist on other columns of the table. The names of those constraints contain information about the nature of theviolation. I wanted to standardize the way I detected these violations and parse out the information. So I just addedcheck constraints with names that followed the naming convention and always look for traditional constraint violationmessages in my perl/DBI script. No, no explicit locking, but as I said, the table being querried is static. -----Original Message----- From: Peter Geoghegan [mailto:peter@2ndquadrant.com] Sent: Thursday, June 16, 2011 9:56 AM To: Gauthier, Dave Cc: Igor Neyman; Mike Christensen; pgsql-general@postgresql.org Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table? On 16 June 2011 14:41, Gauthier, Dave <dave.gauthier@intel.com> wrote: > I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a storedprocedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored proceduresqueries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on(constraint violated or not). > > I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is queryingis a ref table that is very static. This approach may not be bullet proof for checking key relationships in dynamictables. I'll let others speak to that. Did you use explicit locking? If not, you likely have a race condition. The same applies to any sort of enforcement of business rules inside triggers (or, indeed, check constraints). Check constraints are generally intended to enforce simple, immutable rules (i.e. that only reference the tuple that the rule is enforced on). I would have used a trigger instead. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
On 16 Jun 2011, at 7:04, Mike Christensen wrote: > I know I can setup a FK constraint to make sure Table1.ColA exists in > Table2.Key, however what if I want to do the reverse? > > I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do > this with any sort of CHECK constraint, trigger, custom function, etc? > Thanks! Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the samevalue. That would probably be more efficient than executing stored procedure code. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4dfa49a012091645294739!
>> I know I can setup a FK constraint to make sure Table1.ColA exists in >> Table2.Key, however what if I want to do the reverse? >> >> I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do >> this with any sort of CHECK constraint, trigger, custom function, etc? >> Thanks! > > > Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the samevalue. That would probably be more efficient than executing stored procedure code. You'd still have to use a TRIGGER to insert any new or updated values into the third table. Otherwise, you'd have to modify a bunch of code to insert/update the keys into the third table and that somewhat goes against the whole idea of making the database responsible for its own integrity in the first place. What I'm ideally looking for here is a way to ensure the DB cannot possibly exist in this state. Foreign keys let me do that, a trigger (if written correctly) kinda does too so long as the data started out in a valid state and the trigger is always run..
On 16 Jun 2011, at 20:47, Mike Christensen wrote:
>>> I know I can setup a FK constraint to make sure Table1.ColA exists in
>>> Table2.Key, however what if I want to do the reverse?
>>>
>>> I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do
>>> this with any sort of CHECK constraint, trigger, custom function, etc?
>>> Thanks!
>>
>>
>> Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the
samevalue. That would probably be more efficient than executing stored procedure code.
>
> You'd still have to use a TRIGGER to insert any new or updated values
> into the third table. Otherwise, you'd have to modify a bunch of code
> to insert/update the keys into the third table and that somewhat goes
> against the whole idea of making the database responsible for its own
> integrity in the first place.
No you don't.
If Table1.ColA is an FK to Table3.ColA and Table2.ColA is also an FK to Table3.ColA, you can put a unique constraint on
Table3.ColAto make sure the values are unique:
Table1 Table3 Table2
------ ------ ------
ColA >-------|- ColA -|-------< ColA
If you insert a value in either Table1 or Table2, it first HAS to exist in Table3, due to the FK constraints. However,
thatstill allows for values that are in both tables 1 and 3, just pointing to the same value in Table3.
To solve that you add an extra column to all tables, for example:
ALTER TABLE Table1 ADD src CHAR(1) DEFAULT 'A';
ALTER TABLE Table2 ADD src CHAR(1) DEFAULT 'B';
And you change the FK constraints in A and B to include "src":
Table1 Table3 Table2
------ ------ ------
ColA >-------|- ColA -|-------< ColA
src >-/ \-|- src -|-/ \-< src
You also add back a UNIQUE constraint over Table3.ColA (without the "src" column).
Now, if you add a value to Table1, it requires a value of (ColA, 'A') in Table3. If you add one to Table2, it requires
avalue of (ColA, 'B'). If either of those already exist though, you violate the UNIQUE constraint on Table3.ColA.
It's probably convenient to write some triggers to auto-generate the records in Table3, but those triggers are NOT
neededfor relational integrity - they just make the task easier.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4dfaf78612091994554093!
>>>> I know I can setup a FK constraint to make sure Table1.ColA exists in >>>> Table2.Key, however what if I want to do the reverse? >>>> >>>> I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do >>>> this with any sort of CHECK constraint, trigger, custom function, etc? >>>> Thanks! >>> >>> >>> Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have thesame value. That would probably be more efficient than executing stored procedure code. >> >> You'd still have to use a TRIGGER to insert any new or updated values >> into the third table. Otherwise, you'd have to modify a bunch of code >> to insert/update the keys into the third table and that somewhat goes >> against the whole idea of making the database responsible for its own >> integrity in the first place. > > > No you don't. > > If Table1.ColA is an FK to Table3.ColA and Table2.ColA is also an FK to Table3.ColA, you can put a unique constraint onTable3.ColA to make sure the values are unique: > Table1 Table3 Table2 > ------ ------ ------ > ColA >-------|- ColA -|-------< ColA > > If you insert a value in either Table1 or Table2, it first HAS to exist in Table3, due to the FK constraints. However,that still allows for values that are in both tables 1 and 3, just pointing to the same value in Table3. > > To solve that you add an extra column to all tables, for example: > ALTER TABLE Table1 ADD src CHAR(1) DEFAULT 'A'; > ALTER TABLE Table2 ADD src CHAR(1) DEFAULT 'B'; > And you change the FK constraints in A and B to include "src": > > Table1 Table3 Table2 > ------ ------ ------ > ColA >-------|- ColA -|-------< ColA > src >-/ \-|- src -|-/ \-< src > > You also add back a UNIQUE constraint over Table3.ColA (without the "src" column). > > Now, if you add a value to Table1, it requires a value of (ColA, 'A') in Table3. If you add one to Table2, it requiresa value of (ColA, 'B'). If either of those already exist though, you violate the UNIQUE constraint on Table3.ColA. > > It's probably convenient to write some triggers to auto-generate the records in Table3, but those triggers are NOT neededfor relational integrity - they just make the task easier. Yup yup, I see where you're going.. It's like the third table is a "name broker" that grants the unique priveledge of using a name in the database. The other tables will have a FK on it so you'd have to add that name to the table before it can be inserted elsewhere. The third table will be unique which ensures a name is only used once. This would work great, however I'd have to modify a bunch of code to insert a name into the third table before it could be used.. Since an admin tool is the only thing that would be doing this (this data hardly ever changes), this isn't out of the question. I actually don't need any TRIGGERS if I do this, I just need to modify some code. This design will ensure my data is always in a valid state. A fine approach. Thanks! Mike