Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: creating a foreign key referencing a non-primary key

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

From: Tony <andrewst_at_onetel.net.uk>
Date: 18 Mar 2004 04:18:02 -0800
Message-ID: <c0e3f26e.0403180418.6e1ff2a1@posting.google.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US