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) 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org