PostgreSQL foreign key not existing, issue of inheritance?

Question

I am struggling with foreign keys in my DB, possibly it has something to do with inheritance?
So here's the basic setup:

-- table address
CREATE TABLE address
(
  pk_address serial NOT NULL,
  fk_gadmid_0 integer NOT NULL, -- this table already exists, no problem here
  street character varying(100),
  zip character varying(10),
  city character varying(50),
  public boolean,
  CONSTRAINT address_primarykey PRIMARY KEY (pk_address),
  CONSTRAINT gadmid_0_primarykey FOREIGN KEY (fk_gadmid_0)
      REFERENCES adm0 (gadmid_0) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE address OWNER TO postgres;  

-- table stakeholder (parent)
CREATE TABLE stakeholder
(
    pk_stakeholder integer DEFAULT nextval('common_stakeholder_seq') NOT NULL,
    fk_stakeholder_type integer NOT NULL, -- this table also exists, no problem here
    name character varying(255) NOT NULL,
    CONSTRAINT stakeholder_primarykey PRIMARY KEY (pk_stakeholder),
    CONSTRAINT stakeholder_fk_stakeholder_type FOREIGN KEY (fk_stakeholder_type)
        REFERENCES stakeholder_type (pk_stakeholder_type) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
    OIDS=FALSE
);
ALTER TABLE stakeholder OWNER TO postgres;  

-- table individual (child of stakeholder)
CREATE TABLE individual
(
    firstname character varying(50),
    fk_title integer, -- this table also exists, no problem here
    email1 character varying (100),
    email2 character varying (100),
    phone1 character varying (50),
    phone2 character varying (50),
    CONSTRAINT individual_primarykey PRIMARY KEY (pk_stakeholder),
    CONSTRAINT title_foreignkey FOREIGN KEY (fk_title)
        REFERENCES title (pk_title) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (stakeholder)
WITH (
    OIDS=FALSE
);
ALTER TABLE individual OWNER TO postgres;  

-- link between stakeholder and address
CREATE TABLE l_stakeholder_address
(
    pk_l_stakeholder_address serial NOT NULL,
    fk_stakeholder integer NOT NULL REFERENCES stakeholder,
    fk_address integer NOT NULL REFERENCES address,
    CONSTRAINT l_stakeholder_address_primarykey PRIMARY KEY (pk_l_stakeholder_address),
    CONSTRAINT l_stakeholder_address_fk_stakeholder FOREIGN KEY (fk_stakeholder)
        REFERENCES stakeholder (pk_stakeholder) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT l_stakeholder_address_fk_address FOREIGN KEY (fk_address)
        REFERENCES address (pk_address) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH (
    OIDS=FALSE
);
ALTER TABLE l_stakeholder_address OWNER TO postgres;

So far, no problem. Then I tried to add some values:

INSERT INTO individual (pk_stakeholder, fk_stakeholder_type, name, firstname, fk_title, email1, email2, phone1, phone2) 
  VALUES (1, 8, 'Lastname', 'Firstname', 1, '[email protected]', '', '', '');
INSERT INTO address (pk_address, fk_gadmid_0, street, zip, city, public)  
  VALUES (1, 126, 'Address', '', 'City', FALSE);
INSERT INTO l_stakeholder_address (pk_l_stakeholder_address, fk_stakeholder, fk_address)  
  VALUES (DEFAULT, 1, 1);

And finally I end up having an error (SQL state 23503) saying that the key (fk_stakeholder)=(1) is not existing in table "stakeholder".
The first 2 inserts are fine, I can see them in the databases:

stakeholder:
pk_stakeholder | ...
----------------------
1              | ...

address:
pk_address | ...
--------------------
1          | ...

What am I doing wrong? I must admit that I am rather new to PostgreSQL (using 8.4) but I'm not even sure if that is an issue of PG at all, maybe I'm just lacking some basic database design understandings ...
Either way, by now I tried pretty much everything I could think of, I also tried to make the FK deferrable as in PostgreSQL : Transaction and foreign key problem but somehow that doesn't work either.

FuriousFolder · Accepted Answer · 2015-07-31 16:03:42Z

6

You can work around it using additional table individual_pks (individual_pk integer primary key) with all primary keys from both parent and child, which will be maintained using triggers (very simple — insert to individual_pks on insert, delete from it on delete, update it on update, if it changes individual_pk).

Then you point foreign keys to this additional table instead of a child. There'll be some small performance hit, but only when adding/deleting rows.

Or forget inheritance and do it the old way - simply one table with some nullable columns.

edited Jul 31, 2015 at 16:03

FuriousFolder

1,2601 gold badge13 silver badges27 bronze badges

answered Feb 9, 2011 at 10:17

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukas Over a year ago

Thank you very much for your answer, I managed to solve my problem that way. However, instead of 'individual_pks', I rather used 'stakeholder_pk', since stakeholder is the parent table. So 'stakeholder_pk' collects all the primary keys of 'stakeholder' and its children. Thanks!

sierrasdetandil · Accepted Answer · 2014-03-28 15:13:27Z

6

Your analysis is exactly right: It's because of the inheritance. When checking the foreign key, child tables are not considered.

In general, inheritance and foreign keys don't mix well in PostgreSQL. A major problem is that you can't have unique constraints across tables.

Reference

edited Mar 28, 2014 at 15:13

sierrasdetandil

4611 gold badge9 silver badges17 bronze badges

answered Feb 9, 2011 at 4:26

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

5 Comments

Lukas Over a year ago

Thanks for your quick answer. My understanding used to be that child tables don't matter here because they don't have a PK (other than the inherited one) and the FK references the parent table (which has the PK correctly stored). But apparently I'm wrong (although I don't know exactly why). So is there a way at all to do what I want? Maybe using a trigger to check if fk_stakeholder in the link table exists in the PK of stakeholder?

Peter Eisentraut Over a year ago

The primary keys are not the concern here. The foreign key fk_stakeholder points to table stakeholder, but there is no matching row in there. The row in table individuals is not considered.

Lukas Over a year ago

To be honest, I still don't get it. The foreign key points to stakeholder.pk_stakeholder, which exists because it inserted by its child table. I guess that is where the problem lies - but still, the row is there. Just as stakeholder_pk.pk_stakeholder is there in the other solution above. I guess I'm lacking some fundamental understandings in this issue. But nevermind, I don't want to take up any more of your time and it seems to work using the solution proposed by Tometzky. But many thanks for your help anyways!

Peter Eisentraut Over a year ago

Run SELECT * FROM ONLY stakeholder. That's what the FK check does. There is no row there.

matthudson Over a year ago

One solution to the complexity created by inheritance, and especially the expectation that inherited ID attributes be shared: Create a trigger that every child table fires to enforce RI among all children. This is analogous to the single-table RI check but it's not as fast. It does implement the pattern most developers expect once they discover inheritance and start using it on a presumption of net-gain.

Collectives™ on Stack Overflow

PostgreSQL foreign key not existing, issue of inheritance?

2 Answers 2

1 Comment

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Linked

Related