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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With