From: | François Beausoleil <francois(at)teksol(dot)info> |
---|---|
To: | Postgres List <pgsql-general(at)postgresql(dot)org> |
Subject: | State of the art for foreign keys to child tables? |
Date: | 2012-09-18 03:15:05 |
Message-ID: | FCA911B9-63F8-4E21-92B4-E93BB39063E7@teksol.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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:
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?
Thanks!
François
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-09-18 06:35:03 | Re: log_destination = csvlog, line breaks interfere in analysis |
Previous Message | Anibal David Acosta | 2012-09-18 01:42:52 | Re: Windows Services and Postgresql 9.1.3 |