eu tento criar uma tabela pai para a tabela existente, pelo próximo algoritmo:
-- 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);
Mas veja o erro:
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".
E quando tento validar os dados parece que tudo existe:
dev=# SELECT id FROM parent_t1 WHERE id = 2;
id
----
2
não consigo entender o que estou perdendo?
Dos documentos :