Multicolumn foreign keys need useless unique indices?

Поиск
Список
Период
Сортировка
От Antti Haapala
Тема Multicolumn foreign keys need useless unique indices?
Дата
Msg-id Pine.GSO.4.44.0209131019590.17425-100000@paju.oulu.fi
обсуждение исходный текст
Ответы Re: Multicolumn foreign keys need useless unique indices?  ("Christopher Kings-Lynne" <chriskl@familyhealth.com.au>)
Список 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
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






В списке pgsql-hackers по дате отправления:

Предыдущее
От: Jeff Davis
Дата:
Сообщение: Re:
Следующее
От: Hannu Krosing
Дата:
Сообщение: Re: TOAST docs