Re: Problem on foreign key referring to a parent table in PostgreSQL

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Dat Huynh
Sent: Saturday, December 15, 2012 5:39 PM
To: pgsql-general(at)postgresql(dot)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.

---------------------------------------------------------------------

CREATE TABLE p_table

(

id serial NOT NULL,

name text,

CONSTRAINT p_table_pkey PRIMARY KEY (id )

)

WITH (

OIDS=FALSE

);

---------------------------------------------------------------------

CREATE TABLE c_table

(

-- Inherited from table p_table: id integer NOT NULL DEFAULT
nextval('p_table_id_seq'::regclass),

-- Inherited from table p_table: name text,

address text,

CONSTRAINT c_table_pkey PRIMARY KEY (id )

)

INHERITS (p_table)

WITH (

OIDS=FALSE

);

---------------------------------------------------------------------

CREATE TABLE r_table

(

id serial NOT NULL,

ref_id integer,

attr text,

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

)

WITH (

OIDS=FALSE

);

---------------------------------------------------------------------

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.

Do I miss something?

Thank you very much for your help.

Sincerely,

Dat.