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,
compiledby 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
