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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Dat Huynh 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. |
