oracle foreign-key question
jonathan at carnageblender.com
Tue Aug 22 15:33:19 MDT 2006
On Tue, 22 Aug 2006 14:53:26 -0600, "Michael A. Cleverly"
<michael at cleverly.com> said:
> On 8/22/06, Hill, Greg <grhill at corp.untd.com> wrote:
> > 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
> > It seems like a common-enough scenario that Oracle would have some
> > built-in way to handle it, but I haven't found it.
The "right" solution from a normalization standpoint is to extract the
common parts of user and visitor, put that in a separate table, and give
user and visitor a FK to that table. Then your comments table also only
needs a FK to that table.
> This works for me:
> create table temp_comments (
> comment_id integer primary key,
> user_id references temp_users on delete cascade,
> visitor_id references temp_visitors on delete cascade,
> comment_txt varchar(4000) not null,
> -- ...
> check((user_id is null and visitor_id is not null)
> or (user_id is not null and visitor_id is null))
A simpler CHECK would be (user_id is null != visitor_id is null).
C++ is history repeated as tragedy. Java is history repeated as farce. --Scott McKay
More information about the PLUG