oracle foreign-key question

[Jonathan Ellis]

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).

-Jonathan
-- 
C++ is history repeated as tragedy. Java is history repeated as farce.  --Scott McKay