oracle foreign-key question

Michael A. Cleverly michael at cleverly.com
Tue Aug 22 14:53:26 MDT 2006


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

You can have NULL in a column that is a foreign key UNLESS the column
is also defined to be NOT NULL.

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

You want essentially an XOR condition, which you can emulate by doing
a CHECK (see below).

> It seems like a common-enough scenario that Oracle would have some
> built-in way to handle it, but I haven't found it.

This works for me:

create table temp_users (
    user_id integer primary key,
    name    varchar(40) not null
    -- ...
);

create table temp_visitors (
    visitor_id integer primary key,
    ip         varchar(15) not null
    -- ...
);

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

-- Insert some data to test with
insert into temp_users values (1, 'Alice');
insert into temp_users values (2, 'Bob');
insert into temp_visitors values (1, '10.20.30.40');
insert into temp_visitors values (2, '127.0.0.1');
insert into temp_comments values (1, 1, NULL, 'Alice was here');
insert into temp_comments values (2, 1, NULL, 'Alice was here again');
insert into temp_comments values (3, 2, NULL, 'Bob was here');
insert into temp_comments values (4, 2, NULL, 'Bob was here again');
insert into temp_comments values (5, NULL, 1, 'I''m on a private network');
insert into temp_comments values (6, NULL, 2, 'I''m on a loopback interface');

select count(*) as n_users    from temp_users;
select count(*) as n_visitors from temp_visitors;
select count(*) as n_comments from temp_comments;

delete from temp_users where user_id = 1;
select count(*) as n_users    from temp_users;
select count(*) as n_comments from temp_comments;

delete from temp_visitors where visitor_id = 1;
select count(*) as n_visitors from temp_visitors;
select count(*) as n_comments from temp_comments;

Michael



More information about the PLUG mailing list