CODEWITHSUNDEEP
postgresqlinheritance
Marti Markov
Marti Markov

Reputation: 766

Can I have a foreign key to a parent table in PostgreSQL?

I'm using inheritance and I ended up having a problem.

If I run:

select count(*) from estate_properties where id = 86820;

I get 1.

But when I try to run this:

insert into property_images (binary_image, name, property_id) values (16779, 'IMG_0096.jpg', 86820)

I get:

********** Error **********

ERROR: insert or update on table "property_images" violates foreign key constraint "property_images_property_id_fkey" SQL state: 23503 Detail: Key (property_id)=(86820) is not present in table "estate_properties".

Also ID on estate_properties is SERIAL.

Note: Another table apartments inherits from estate_properties, and 86820 was added to it. Would that make a difference? Also why would it I still have the ID in the parent table and I can select if from there.

Edit: Looking more closely at the documentation:

http://www.postgresql.org/docs/9.5/static/ddl-inherit.html

I want to achieve this:

5.9.1. Caveats

  • Specifying that another table's column REFERENCES cities(name) would
    allow the other table to contain city names, but not capital names.
    There is no good workaround for this case.

EDIT2: Here is the declaration of the foreign key:

CONSTRAINT property_images_property_id_fkey FOREIGN KEY (property_id)
      REFERENCES estate_properties (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

Upvotes: 7

Views: 3041

Answers (1)

Marti Markov
Marti Markov

Reputation: 766

Apparently the answer is here:

Foreign keys + table inheritance in PostgreSQL?

A foreign key can point to a table that is part of an inheritance hierarchy, but it'll only find rows in that table exactly. Not in any parent or child tables. To see which rows the foreign key sees, do a SELECT * FROM ONLY thetable. The ONLY keyword means "ignoring inheritance" and that's what the foreign key lookup will do

Upvotes: 6

Related Questions