Re: optional foreign key

Why don't you use a trigger to check this? When a record is inserted, have the trigger check for your special character or the existence in another table. If the check passes, do nothing. If the check fails, drop the record from the table.

HTH,
Brian

Michel Cadot wrote:
>
> You cannot do that: there is an integrity constraint or there is not.
> But column link null verifies this constraint. So you can use
>
> create table bar
> (
> id number primary key,
> special char(1) check (special is null or special='X'),
> link number references foo,
> constraint chk check ((special = 'X' and link is null)
> or (special is null and link is not null))
> );
>
> SQL>insert into foo values (1);
>
> 1 row created.
>
> SQL>insert into bar values (1,null,1);
>
> 1 row created. ==> Correct not special and references the row in foo
>
> SQL>insert into bar values (2,'X',null);
>
> 1 row created. ==> Correct special and no reference
>
> SQL>insert into bar values (3,'X',1);
> ==> Wrong special and references
> insert into bar values (3,'X',1)
> *
> ERROR at line 1:
> ORA-02290: check constraint (.CHK) violated
>
> SQL>insert into bar values (4, null, 2);
> ==> Wrong not special and references an inexistent row
> insert into bar values (4, null, 2)
> *
> ERROR at line 1:
> ORA-02291: integrity constraint (MMA$EP34087.SYS_C004499) violated - parent key
> not found
>
> ...
>
> Roy Smith a écrit dans le message ...
> >I've got a table something like this:
> >
> >create table foo
> >(
> > id number primary key;
> >);
> >
> >create table bar
> >(
> > id number primary key,
> > link references foo
> >);
> >
> >The problem is, there will be exceptional cases where I want to put a
> >value in bar.link which doesn't exist in foo.id. What I'd like is
> >something like this:
> >
> >create table bar
> >(
> > id number primary key,
> > special char(1),
> > link constraint check (special = 'X' or references foo)
> >);
> >
> >Obviously, that's made-up syntax, but that's the idea I want. Any way to
> >do something like this?
> >
> >The alternative would be to just add dummy "place-holder" entries in foo
> >to handle these exceptional cases, but I'd rather not do that if I can
> >avoid it.
> >
> >--
> >Roy Smith <roy_at_popmail.med.nyu.edu>
> >New York University School of Medicine
> >
Received on Tue Aug 24 1999 - 11:49:33 CDT