From: "Greg Patnude" <gpatnude@hotmail.com>
To: "'Jan Wieck'" <JanWieck@Yahoo.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Unique Constraint with foreign Key
Date: Tue, 17 Feb 2004 09:22:44 -0800
Message-ID: <000301c3f57a$a7d95800$6401a8c0@dpanel.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <40322829.3050603@Yahoo.com>
Importance: Normal

You've got it wrong when you reference the data column (a.x) -- your
foreign key should reference the primary key in the referenced table
(a.y)... 

Besides,  in your table A -- 1, 99 2, 99 violates your unique constraint
on column 'X' -- it would never happen... 

What I suggested is like this:

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 integer NOT NULL REFERENCES a(y),
 
);

Table A would have 

1, 99
2, 99
..

99,99

and table B would have

1, 1
1, 2
..
1, 99


Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID. 83835
(208) 762-0762
 
Send replies to: gpatnude@adelphia.net

Website: http://www.left-center.com

-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com] 
Sent: Tuesday, February 17, 2004 6:42 AM
To: Greg Patnude
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 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 #