Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 924
Marti Markov Avatar asked Feb 17 '16 03:02

Marti Markov


1 Answers

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

like image 104
Marti Markov Avatar answered Nov 15 '22 07:11

Marti Markov