Re: Primary key and references

On Tue, 18 Mar 2003, Shridhar Daithankar<shridhar_daithankar(at)persistent(dot)co(dot)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