From: | "Shridhar Daithankar<shridhar_daithankar(at)persistent(dot)co(dot)in>" <shridhar_daithankar(at)persistent(dot)co(dot)in> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Primary key and references |
Date: | 2003-03-18 14:22:42 |
Message-ID: | 200303181952.42055.shridhar_daithankar@persistent.co.in |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-03-18 14:30:38 | Re: Nested transactions |
Previous Message | Manfred Koizar | 2003-03-18 09:32:58 | Re: Nested transactions |