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

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