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.
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 |