0

I'm trying to add a foreign key constraint to an existing table:

ALTER TABLE address 
  ADD FOREIGN KEY(company_id) 
  REFERENCES company(company_id) ON DELETE CASCADE DEFERRABLE;

That fails with:

ERROR:  insert or update on table "address" violates \
        foreign key constraint "address_company_id_fkey"
DETAIL:  Key (company_id)=(83376) is not present in table "company".

Yet the company table does have that key:

DB=> SELECT company_id FROM company WHERE company_id = 83376;
 company_id 
------------
      83376
(1 row)

I suspect that this is due to table inheritance (old database, very historic reasons), company is a base table and one derived table is the person table. Which is the one containing the actual key:

DB=> SELECT company_id FROM person WHERE company_id = 83376;
 company_id 
------------
      83376
(1 row)

I'm specifically targeting the base table (assuming it contains the data of all derived tables) because the address rows refer to different derived tables.

Is there a way to make that work?

Or as an alternative, kinda even better, is there a way to have foreign keys targeting specific derived tables?

hnh
  • 13,957
  • 6
  • 30
  • 40
  • 2
    See the chapter "Caveats" in the manual: https://www.postgresql.org/docs/current/static/ddl-inherit.html#DDL-INHERIT-CAVEATS –  May 18 '17 at 14:41
  • I was kinda hoping for 'These deficiencies will probably be fixed in some future release' - this statement in the dox must be like 15 years old :-) – hnh May 18 '17 at 14:44
  • Beyond table partitioning, table inheritance has little use in PostgreSQL (to be fair, it is a rare implementation anyway). But you can achieve something similar with N [one-to-one relations](http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) in any RDBMS (where N in the number of subtypes). – pozs May 19 '17 at 08:48
  • @pozs your comment seems a little off-topic, but to give some context, this particular schema used table inheritance to workaround the lack of writable views back then (~15y ago). PG wasn't always the beauty it is today :-) – hnh May 19 '17 at 09:01
  • @hnh which part seems off-topic? I mentioned partitioning because inheritance is mainly used for that (at least in production). Otherwise an inheritance model is generally structured with relations. But yeah, writable views can actually "hide" this detail nowadays. – pozs May 19 '17 at 09:10
  • A 'little', not completely ;-) My question was specifically about foreign keys not about what table inheritance is. But I understand that you just tried to help and provide extra info, thanks a lot! @a_horse_with_no_name, if you want to post your comment as an answer, I would accept it. – hnh May 19 '17 at 09:19

0 Answers0