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.
