Hi,

Today I discovered that if there is a compund primary key on a table, I can 
not create a reference from another table to one of the fields in the primary 
key..

Look at this..

phd=# create table tmp1(a integer,b integer,primary key(a,b));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for 
table 'tmp1'
CREATE TABLE
phd=# create table tmp2(a integer references tmp1(a));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  UNIQUE constraint matching given keys for referenced table "tmp1" not 
found
phd=# drop table tmp1;
DROP TABLE
phd=# create table tmp1(a integer unique,b integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for 
table 'tmp1'
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'tmp1_a_key' for 
table 'tmp1'
CREATE TABLE
phd=# create table tmp2(a integer references tmp1(a));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
phd=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
(1 row)


Note that I do not require unique check on tmp2. It is perfectly acceptable to 
have duplicate values in table tmp2. However no duplicates are allowed in 
table tmp1.

I consider this as a bug but given my understanding of sql, I won't count on 
it. Any comments? 

The workaround shown here is acceptable as I don't really need a compound 
primary key. But If I need, I know it won't work..

TIA..

 Shridhar

Tue, 18 Mar 2003 19:52:42 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Primary key and references
On Tue, 18 Mar 2003, Shridhar Daithankar<shridhar_daithankar@persistent.co.in> wrote:

> Hi,
> 
> Today I discovered that if there is a compund primary key on a table, I can 
> not create a reference from another table to one of the fields in the primary 
> key..
> 
> Look at this..
> 
> phd=# create table tmp1(a integer,b integer,primary key(a,b));
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for 
> table 'tmp1'
> CREATE TABLE
> phd=# create table tmp2(a integer references tmp1(a));
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> ERROR:  UNIQUE constraint matching given keys for referenced table "tmp1" not 
> found
> phd=# drop table tmp1;
> DROP TABLE
> phd=# create table tmp1(a integer unique,b integer primary key);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'tmp1_pkey' for 
> table 'tmp1'
> NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'tmp1_a_key' for 
> table 'tmp1'
> CREATE TABLE
> phd=# create table tmp2(a integer references tmp1(a));
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE TABLE
> phd=# select version();
>                                version
> ---------------------------------------------------------------------
>  PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4
> (1 row)
> 

That's right. The a,b combination is unique not the individual
fields. Consider:

Table:
 a | b
-------
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 2 | 2
 3 | 1


Picking just a couple of examples from that a = 1 several times and b = 1
several times but there is no unique constraint violation because there isn't
something like a = 1 and b = 1 as a combination appearing more than once.

> 
> Note that I do not require unique check on tmp2. It is perfectly acceptable to 
> have duplicate values in table tmp2. However no duplicates are allowed in 
> table tmp1.
> 
> I consider this as a bug but given my understanding of sql, I won't count on 
> it. Any comments? 

If a is to be referenced in a foreign key it needs to be unique or how could it
it be known which of the rows with a given value are being refered to. It
follows that if a can be referenced in a foreign key then a uniquely identifies
a row in the referenced table and therefore a primary key of (a,b) necessarily
is unique based solely on a, i.e. the (a,b) combination seems unlikely to be
the primary key for the table.

> 
> The workaround shown here is acceptable as I don't really need a compound 
> primary key. But If I need, I know it won't work..

I hope that helps.

> 
> TIA..
> 
>  Shridhar


--
Nigel J. Andrews


Tue, 18 Mar 2003 14:37:52 +0000 (GMT)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: Primary key and references
On Tuesday 18 Mar 2003 8:07 pm, Nigel J. Andrews wrote:
> On Tue, 18 Mar 2003, Shridhar 
Daithankar<shridhar_daithankar@persistent.co.in> wrote:
> > I consider this as a bug but given my understanding of sql, I won't count
> > on it. Any comments?
>
> If a is to be referenced in a foreign key it needs to be unique or how
> could it it be known which of the rows with a given value are being refered
> to. It follows that if a can be referenced in a foreign key then a uniquely
> identifies a row in the referenced table and therefore a primary key of
> (a,b) necessarily is unique based solely on a, i.e. the (a,b) combination
> seems unlikely to be the primary key for the table.

Hmm.. So I need to create unique constraint on original column. OK. Got it 
now..

 Shridhar

Tue, 18 Mar 2003 20:30:16 +0530
"Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
	<shridhar_daithankar@persistent.co.in>
Re: Primary key and references