Re: optional foreign key

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 - 10:47:42 CDT