0

i try to create parent table for exists table, by next algorithm:

-- CREATE TABLE
CREATE TABLE t1 (id serial PRIMARY KEY);
CREATE TABLE foreign_t1 (id serial, t1_id integer REFERENCES t1 (id));
-- insert data
INSERT INTO t1
SELECT * FROM generate_series(1,4);
INSERT INTO foreign_t1
SELECT generate_series, generate_series % 3 + 1
FROM generate_series(1,9);
-- CRETAE PARENT table
CREATE TABLE parent_t1 (LIKE t1 including all);
-- ALTER t1 DO it inherit of the parent
ALTER TABLE t1 INHERIT parent_t1;
-- TRY TO MOVE FK TO the parent
ALTER TABLE foreign_t1 DROP CONSTRAINT foreign_t1_t1_id_fkey;
ALTER TABLE foreign_t1 ADD CONSTRAINT
foreign_t1_t1_id_fkey FOREIGN KEY(t1_id)
REFERENCES parent_t1 (id);

But cath the error:

ERROR:  insert or update on table "foreign_t1" violates foreign key constraint "foreign_t1_t1_id_fkey"
DETAIL:  Key (t1_id)=(2) is not present in table "parent_t1".

And when i try to validate the data looks like all exists:

dev=# SELECT id FROM parent_t1 WHERE id = 2;
 id 
----
  2

i can't understand what i missing?

1

From the docs:

A serious limitation of the inheritance feature is that....foreign key constraints only apply to single tables, not to their inheritance children.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.