Search Postgresql Archives

Re: State of the art for foreign keys to child tables?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux