MIME-Version: 1.0
From: Michael Corey <michael.corey.ap@nielsen.com>
Date: Thu, 18 Apr 2024 13:47:19 -0400
Message-ID: <CAABu8T_ZZAFJykKFb-q3ZF8QWLDV8zXNP0jx7QPb6wWVE+_o2A@mail.gmail.com>
Subject: Foreign Key error between two partitioned tables
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000039dfd306166292ec"
Archived-At: <https://www.postgresql.org/message-id/CAABu8T_ZZAFJykKFb-q3ZF8QWLDV8zXNP0jx7QPb6wWVE%2B_o2A%40mail.gmail.com>
Precedence: bulk

--00000000000039dfd306166292ec
Content-Type: text/plain; charset="UTF-8"

Hello,

I receive the following error when creating a foreign key between two
partitioned tables.
ERROR:  there is no unique constraint matching given keys for referenced
table "par_log_file"

Here is my setup:

CREATE TABLE par_log_file (
    par_file_id character varying(20) NOT NULL,
    par_id character varying(64) NOT NULL
) PARTITION BY RANGE (par_file_id)  ;

ALTER TABLE ONLY par_log_file
    ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_id);

--

CREATE TABLE par_log_definition (
    par_file_id character varying(20) NOT NULL,
    tif_seq_nmbr bigint NOT NULL,
    name_25 character varying(255)
) PARTITION BY RANGE (par_file_id)  ;


ALTER TABLE ONLY par_log_definition
    ADD CONSTRAINT pld_pk PRIMARY KEY (par_file_id, tif_seq_nmbr);

CREATE INDEX pld_idx ON par_log_definition USING btree (par_file_id);

ALTER TABLE par_log_definition
    ADD CONSTRAINT pld_fk FOREIGN KEY (par_file_id) REFERENCES
par_log_file(par_file_id);

Version Postgres 13.13

Any help would be appreciated/


-- 
Michael Corey

--00000000000039dfd306166292ec
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:verdana,=
sans-serif">Hello,</div><div class=3D"gmail_default" style=3D"font-family:v=
erdana,sans-serif"><br></div><div class=3D"gmail_default" style=3D""><font =
face=3D"verdana, sans-serif">I receive the following error when creating a =
foreign key between two partitioned tables. =C2=A0</font><br><font face=3D"=
verdana, sans-serif">ERROR: =C2=A0there is no unique constraint matching gi=
ven keys for referenced table &quot;par_log_file&quot;</font><br><br><font =
face=3D"verdana, sans-serif">Here is my setup:</font><br><br><font face=3D"=
monospace">CREATE TABLE par_log_file (<br>=C2=A0 =C2=A0 par_file_id charact=
er varying(20) NOT NULL,<br>=C2=A0 =C2=A0 par_id character varying(64) NOT =
NULL<br>) PARTITION BY RANGE (par_file_id) =C2=A0;<br><br>ALTER TABLE ONLY =
par_log_file<br>=C2=A0 =C2=A0 ADD CONSTRAINT plf_pk PRIMARY KEY (par_file_i=
d);<br><br>--<br><br>CREATE TABLE par_log_definition (<br>=C2=A0 =C2=A0 par=
_file_id character varying(20) NOT NULL,<br>=C2=A0 =C2=A0 tif_seq_nmbr bigi=
nt NOT NULL,<br>=C2=A0 =C2=A0 name_25 character varying(255)<br>) PARTITION=
 BY RANGE (par_file_id) =C2=A0;<br><br><br>ALTER TABLE ONLY par_log_definit=
ion<br>=C2=A0 =C2=A0 ADD CONSTRAINT pld_pk PRIMARY KEY (par_file_id, tif_se=
q_nmbr);<br><br>CREATE INDEX pld_idx ON par_log_definition USING btree (par=
_file_id);<br><br>ALTER TABLE par_log_definition<br>=C2=A0 =C2=A0 ADD CONST=
RAINT pld_fk FOREIGN KEY (par_file_id) REFERENCES par_log_file(par_file_id)=
;</font><br></div><div class=3D"gmail_default" style=3D"font-family:verdana=
,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-family:ve=
rdana,sans-serif">Version Postgres 13.13</div><div class=3D"gmail_default" =
style=3D"font-family:verdana,sans-serif"><br></div><div class=3D"gmail_defa=
ult" style=3D"font-family:verdana,sans-serif">Any help would be appreciated=
/</div><div class=3D"gmail_default" style=3D"font-family:verdana,sans-serif=
"><br></div><div><br></div><span class=3D"gmail_signature_prefix">-- </span=
><br><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_sig=
nature"><div dir=3D"ltr"><font face=3D"comic sans ms, sans-serif">Michael C=
orey</font></div></div></div>

--00000000000039dfd306166292ec--