State of the art for foreign keys to child tables?

Поиск
Список
Период
Сортировка
От François Beausoleil
Тема State of the art for foreign keys to child tables?
Дата
Msg-id FCA911B9-63F8-4E21-92B4-E93BB39063E7@teksol.info
обсуждение исходный текст
Ответы Re: State of the art for foreign keys to child tables?
Список pgsql-general
Дерево обсуждения
What's the state of the art for foreign keys on child tables?

My use case is this:

CREATE TABLE parties(party_id serial primary key);
CREATE TABLE positions( PRIMARY KEY(party_id) ) INHERITS(parties);
CREATE TABLE organizations( PRIMARY KEY(party_id) ) INHERITS(parties);
CREATE TABLE party_names( party_id int REFERENCES parties, surname text, PRIMARY KEY(party_id, surname) );

INSERT INTO organizations VALUES (1);
INSERT INTO party_names VALUES (1, 'foo');

This currently fails with:

ERROR:  insert or update on table "party_names" violates foreign key constraint "party_names_party_id_fkey"
DETAIL:  Key (party_id)=(1) is not present in table "parties".

I found http://stackoverflow.com/questions/10252603/parent-and-child-table-foreign-key which suggests using something like this:

CREATE RULE parties_ref
AS ON INSERT TO party_names
WHERE new.party_id NOT IN (SELECT party_id FROM parties)
DO INSTEAD NOTHING;

When using that and no foreign key reference, then the INSERT "succeeds" in inserting 0 records, which doesn't raise an exception... Then I found older posts on this mailing list:


These mention using triggers to reproduce foreign key checks.

Is that information still current as of 9.2?

Thanks!
François

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