Postgres Pro
Downloads
Home   >   mailing lists

Thread: primary key of partitioned table referenced by foreign table, v11,v12 clarification

primary key of partitioned table referenced by foreign table, v11,v12 clarification

From
Raj kumar
Date:
Hi Team,

I am getting the below error when I am referencing Primary Key of parents table from checking table in Postgres version 11. I went through documentation of Postgres 12, where is mentioned that, "Primary key of the partitioned table can be referenced by other foreign tables."

I would like to know if the below error is something that is fixed or something else.

test=# insert into checking values(1,'tom',1);
ERROR:  insert or update on table "checking" violates foreign key constraint "checking_artist_fkey"
DETAIL:  Key (artist)=(1) is not present in table "parent".
test=#

Re: primary key of partitioned table referenced by foreign table,v11, v12 clarification

From
Jeff Janes
Date:
On Thu, Feb 20, 2020 at 4:45 AM Raj kumar <rajkumar820999@gmail.com> wrote:
Hi Team,

I am getting the below error when I am referencing Primary Key of parents table from checking table in Postgres version 11. I went through documentation of Postgres 12, where is mentioned that, "Primary key of the partitioned table can be referenced by other foreign tables."

Is that an exact quote?  I can't find the quoted text anywhere.  There is a similar sounding item in the v12 release notes "Allow foreign keys to reference partitioned tables".  And if it is in the v12 release notes, then we can safely say it not present in v11.

 
I would like to know if the below error is something that is fixed or something else.

test=# insert into checking values(1,'tom',1);
ERROR:  insert or update on table "checking" violates foreign key constraint "checking_artist_fkey"
DETAIL:  Key (artist)=(1) is not present in table "parent".
test=#

Probably, but without the CREATE statements for the tables and partitions and foreign keys it is hard to give a definitive answer.

Cheers,

Jeff