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