Re: Unique Constraint with foreign Key

Greg Patnude wrote:
> Pleas also note that the referenced column in the foreign table either needs
> to be the PRIMARY KEY or have a unique constraint on it or maybe it just
> requires an index on it -- I'm not sure but I discovered that if the column
> in the foreign table (containing the REFERENCED key...) is NOT the primary
> key column -- the REFERENCES a(x) will faill unless a.x is specified as
> 'UNIQUE' -- as in the following example:

This is according to the SQL specification, which doesn't like doubts.

Imagine rows (1, 99), (2, 99) in table a and row (3, 99) in table b. 
Which of the a-rows is now referenced and am I allowed to delete the 
other? There are good arguments either way, but if you require a UNIQUE 
on a.x, then this question will never come up.


Jan

> 
> create table a (
> y integer not null primary key default nextval('nexta_seq'),
> x varchar not null UNIQUE
> 
> );
> 
> create table b (
> 
>     z integer not null PRIMARY KEY default nextval('nextbz_seq'),
>     x varchar NOT NULL REFERENCES a(x),
> 
> );
> 
> 


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #