Problem on foreign key referring to a parent table in PostgreSQL

From: Dat Huynh <htdatcse(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem on foreign key referring to a parent table in PostgreSQL
Date: 2012-12-15 22:39:09
Message-ID: CAPw=odgDL3_8g_wN_eO+Do6ukZGziM5Zoc0=kdWS_VSmS8CGQg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-12-15 23:05:34 Re: Problem on foreign key referring to a parent table in PostgreSQL
Previous Message Peter Bex 2012-12-15 21:22:15 Re: Need help for import of text file