oracle foreign-key question
grhill at corp.untd.com
Tue Aug 22 13:40:25 MDT 2006
I'm doubtful that I can make this work how I want it to without doing
something odd, but maybe someone knows a way.
I have a situation like this:
A user table with primary key user_id
A visitor table with primary key visitor_id
A comment table with foreign key relationships to user_id and visitor_id
(on delete cascade for both)
The comment table should require either a user_id or a visitor_id, never
both. So, I want to be able to set one or the other to NULL in the
comment table, but still have deletes from user or visitor cascade to
the comment table. The problem is, I don't believe that Oracle will
allow me to put NULL in an on delete cascade foreign key because there
is no NULL entry in the parent table.
Anyone know of an elegant solution to this, or do I just need to do some
sort of hack? I could put NULL values in the parent tables, but I'd
have to modify the schema to allow it. I could also drop the foreign
keys and just put triggers in both parent tables to mimic on delete
cascade. Neither solution seems elegant to me.
It seems like a common-enough scenario that Oracle would have some
built-in way to handle it, but I haven't found it.
More information about the PLUG