Обсуждение: constraint/rule/trigger - insert into table X where not in table Y

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'

I know this is ideally done in the app, but in the interest of time to market i'm looking into
a simple DB solution.

I know i can do this with a trigger, but was wondering if there was a better way, like using
a constraint or a rule (seems like that would be pretty slick - but i can't wrap my head
around how to make it work)

Thanks

Dave

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!