On Tue, 18 Mar 2003, Shridhar Daithankar<[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
