François Beausoleil wrote: > 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: > > http://postgresql.1045698.n5.nabble.com/Foreign-keys-to-inherited-tables-td1900234.html > http://postgresql.1045698.n5.nabble.com/Inheritance-on-foreign-key-td1924951.html > http://postgresql.1045698.n5.nabble.com/Partitioned-Tables-Foreign-Key-Constraints-Problem- > td2066267.html > > These mention using triggers to reproduce foreign key checks. > > Is that information still current as of 9.2? I'm afraid that a trigger is still the best you can do. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
