The primary key only ensures uniqueness on the specific table that you are working with, not across an entire inheritance chain. Likewise, Foreign Keys are linked to explicit tables and not the inheritance chain as a whole.
In your example “p_table” does NOT have value of “2”, “c_table” is where that value is stored and the FK only refers to data explicitly stored within “p_table”.
In effect a foreign key defaults to (and cannot be changed from) REFERENCES p_table “ONLY” (id) while by default SELECT FROM “p_table” means “and all children” but it can be changed to mean “ONLY” if desired.
David J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dat Huynh
Sent: Saturday, December 15, 2012 5:39 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Problem on foreign key referring to a parent table in PostgreSQL
Dear all,
I'm new to PostgreSQL. I currently have a problem with the foreign key constraint to a parent table in PostgreSQL.
I have three tables p_table, c_table, and r_table as the following.
---------------------------------------------------------------------
CONSTRAINT p_table_pkey PRIMARY KEY (id )
---------------------------------------------------------------------
-- Inherited from table p_table: id integer NOT NULL DEFAULT nextval('p_table_id_seq'::regclass),
-- Inherited from table p_table: name text,
CONSTRAINT c_table_pkey PRIMARY KEY (id )
---------------------------------------------------------------------
CONSTRAINT r_table_pkey PRIMARY KEY (id ),
CONSTRAINT r_table_ref_id_fkey FOREIGN KEY (ref_id)
REFERENCES p_table (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
---------------------------------------------------------------------
The table "c_table" inherits the table "p_table". The table "r_table" has a foreign key "ref_id" referring to the table "p_table".
Then I insert a row with the ID 1 into the table "p_table" and a row with the ID 2 into the table "c_table".
By using SELECT statement, I can see that the table "p_table" has two rows with two IDs 1 and 2.
I wonder why I CAN insert a row with a foreign key value 1 into "r_table" but I CANNOT insert a row with the foreign key value 2 into the table "r_table".
Obviously, when I run SELECT statement on the table "p_table", it returns two rows with the ids 1 and 2.
Thank you very much for your help.