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
CASCADEON 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
