From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | David Kerr <dmk(at)mr-paradox(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: constraint/rule/trigger - insert into table X where not in table Y |
Date: | 2010-07-14 18:18:46 |
Message-ID: | 2FB22EA0-5351-49E4-A682-DD0A7CB21BAC@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 14 Jul 2010, at 18:13, David Kerr wrote:
> Howdy,
>
> I'm trying to think of the best way to handle this situation.
>
> I've got 2 tables, X and Y
>
> Table X has a field foo varchar(20)
> Table Y has a field bar varchar(20)
>
> I want to enforce, that if table X.foo = 'dave' then you can't insert (or update) Y.bar = 'dave'
You could add a table Z (
baz varchar(20) PRIMARY KEY,
foo varchar(20) REFERENCES X (foo),
bar varchar(20) REFERENCES Y (bar),
CHECK ((foo IS NULL AND bar IS NOT NULL) OR (foo IS NOT NULL AND bar IS NULL))
)
You can populate this table from a rule or trigger.
OTOH, if you have a trigger anyway, you can move the checks in there as well.
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,4c3dff91286211140418644!
From | Date | Subject | |
---|---|---|---|
Next Message | Bill Thoen | 2010-07-14 18:21:57 | How to Declare Functions Containing OUT PArameters? |
Previous Message | Joshua D. Drake | 2010-07-14 18:06:15 | Re: Any ideas on Version 9.0 production release date? |