Re: creating a foreign key referencing a non-primary key

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<4058c70c$0$8360$afc38c87_at_news.optusnet.com.au>...
> You can't reference a unique key, either, because although the ambiguity
> problem is dealt with by declaring the parent column unique, NULL entries
> are permitted to exist in the parent table's key column, and that would also
> cause massive problems when joining the child to the parent, because null
> never equals null (or anything else for that matter).

Sure you can. The foreign key is only enforced if a non-null entry is made in the child table's foreign key column:

SQL> create table p (p_id integer primary key, p_name varchar2(100) unique);

Table created.

SQL> desc p

 Name                            Null?    Type
 ------------------------------- -------- ----
 P_ID                            NOT NULL NUMBER(38)
 P_NAME                                   VARCHAR2(100)

SQL> create table c (c_id integer primary key, p_name references p(p_name) );

Table created.

SQL> insert into p values (1,'AAA');

1 row created.

SQL> insert into c values (100,'AAA');

1 row created.

SQL> insert into c values (200,NULL);

1 row created.

SQL> insert into c values (300,'BBB');
insert into c values (300,'BBB')
*
ERROR at line 1:
ORA-02291: integrity constraint (TANDREWS.SYS_C00155371) violated - parent key
not found Received on Thu Mar 18 2004 - 06:18:02 CST