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

"Tony" <andrewst_at_onetel.net.uk> wrote in message news:c0e3f26e.0403180418.6e1ff2a1_at_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:

Slight misunderstanding, I think. Probably my fault because of my imprecision of expression. I was trying to say that you can't dodge the foreign key issue by making the column in the parent table a unique key rather than a primary one, because that would permit nulls in the *parent* column.

Mr King in Scott's EMP table is a classic example of the *child* being permitted to have nulls, no disagreement there at all. But not the parent key column, which is the point I was attempting to make, and which your demo doesn't actually contradict.

Regards
HJR
>
> 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:31:37 CST