Re: 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 | 11 | 21 | 32 | 12 | 23 | 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