Re: Constraint to ensure value does NOT exist in another table?

Поиск
Список
Период
Сортировка
От Alban Hertroys
Тема Re: Constraint to ensure value does NOT exist in another table?
Дата
Msg-id F703F446-719A-4C0D-BC53-164DEF60F0B1@solfertje.student.utwente.nl
обсуждение исходный текст
Ответ на Re: Constraint to ensure value does NOT exist in another table?  (Mike Christensen <mike@kitchenpc.com>)
Ответы Re: Constraint to ensure value does NOT exist in another table?  (Mike Christensen <mike@kitchenpc.com>)
Список pgsql-general
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!



В списке pgsql-general по дате отправления:

Предыдущее
От: Achilleas Mantzios
Дата:
Сообщение: Re: PostgreSQL 9.0 or 9.1 ?
Следующее
От: Alban Hertroys
Дата:
Сообщение: Re: PostgreSQL 8.4.8 bringing my website down every evening