Multicolumn foreign keys need useless unique indices?

From: Antti Haapala <antti(dot)haapala(at)iki(dot)fi>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Multicolumn foreign keys need useless unique indices?
Date: 2002-09-13 08:18:03
Message-ID: Pine.GSO.4.44.0209131019590.17425-100000@paju.oulu.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


There is a misfeature in 7.2.2 that appears when I have a foreign key that
references two columns of a table. Consider following simplified example:

CREATE TABLE a (
a int PRIMARY KEY,
b int
);

CREATE TABLE b (
aref int,
bref int,
FOREIGN KEY (aref, bref) REFERENCES a(a, b)
MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE
);

I get an error

"UNIQUE constraint matching given keys for referenced table "a" not
found."

because I have unique constraint only on the first field (which is still
enough to make the whole combination unique. (b is not even unique))...

So I need to add an useless(?) UNIQUE constraint to "(a, b)" for table "a"
just to allow creation of multicol FOREIGN KEYs for table "b".

And I get NOTICE: CREATE TABLE / UNIQUE will create implicit index
'a_a_key' for table.

AFAIK, the extra index only slows down my inserts - it basically contains
no usable information... shouldn't the presence of _primary_key_ in
multicol foreign key be enough to decide whether the whole key is unique
or not? And shouldn't it be enough to find out the tuple in table 'a'
corresponding newly inserted tuple in b?

Or should I just write my own triggers for checking the integrity of
"b"/"bref" column pair to avoid needless index creation?

--
Antti Haapala

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Kings-Lynne 2002-09-13 08:27:20 Re: Multicolumn foreign keys need useless unique indices?
Previous Message Jeff Davis 2002-09-13 06:58:00 Re: