Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p" - Mailing list pgsql-general

From Adrian Klaver
Subject Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"
Date
Msg-id f9fc06d7-c8c1-31ef-4b8f-107f5385a611@aklaver.com
Whole thread Raw
In response to ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"  (Shatamjeev Dewan <sdewan@nbsps.com>)
List pgsql-general
On 11/18/19 9:10 AM, Shatamjeev Dewan wrote:
> Hi Michael,
> 
> I am trying to create a foreign key constraint on a table : audit_param 
> in postgres 12 which references  partitioned table audit_p. is there 
> anyway to get rid of this error.
> 
> *ERROR:  there is no unique constraint matching given keys for 
> referenced table "audit_p"*
> 
>                                        ^
> 
> sd_tems_partition_test=# *CREATE TABLE audit_logging.audit_p*(
> 
> sd_tems_partition_test(#     id BIGINT   NOT NULL,
> 
> sd_tems_partition_test(#     event_id BIGINT  NOT NULL,
> 
> sd_tems_partition_test(#     caused_by_user_id BIGINT  NOT NULL,
> 
>          PARTITION BY RANGE(create_dtt);
> 
>          sd_tems_partition_test(#     -- additional user information (to 
> be defined by the application)
> 
> sd_tems_partition_test(#     adtl_user_info BIGINT,
> 
> sd_tems_partition_test(#     create_dtt TIMESTAMP DEFAULT now()  NOT NULL,
> 
> sd_tems_partition_test(#     CONSTRAINT audit_PK1 PRIMARY KEY 
> (id,create_dtt))
> 
> sd_tems_partition_test-# PARTITION BY RANGE(create_dtt);
> 
> CREATE TABLE
> 
> sd_tems_partition_test=# \d audit_logging.audit_p
> 
>                      Partitioned table "audit_logging.audit_p"
> 
>        Column       |            Type             | Collation | Nullable 
> | Default
> 
> -------------------+-----------------------------+-----------+----------+---------
> 
> id                | bigint                      |           | not null |
> 
> event_id          | bigint                      |           | not null |
> 
> caused_by_user_id | bigint                      |           | not null |
> 
> adtl_user_info    | bigint                      |           |          |
> 
> create_dtt        | timestamp without time zone |           | not null | 
> now()
> 
> Partition key: RANGE (create_dtt)
> 
> Indexes:
> 
>      "audit_pk1" PRIMARY KEY, btree (id, create_dtt)
> 
> Number of partitions: 0
> 
> sd_tems_partition_test*=# CREATE TABLE audit_logging.audit_param (*
> 
> *sd_tems_partition_test(#     audit_id BIGINT  NOT NULL,*
> 
> sd_tems_partition_test(#     param_position SMALLINT  NOT NULL,
> 
> sd_tems_partition_test(#     value CHARACTER VARYING(4096)  NOT NULL,
> 
> sd_tems_partition_test(#     CONSTRAINT audit_param_PK PRIMARY KEY 
> (audit_id)
> 
> sd_tems_partition_test(# );
> 
> CREATE TABLE
> 
> sd_tems_partition_test=# \d audit_logging.audit_param;
> 
>                       Table "audit_logging.audit_param"
> 
>       Column     |          Type           | Collation | Nullable | Default
> 
> ----------------+-------------------------+-----------+----------+---------
> 
> audit_id       | bigint                  |           | not null |
> 
> param_position | smallint                |           | not null |
> 
> value          | character varying(4096) |           | not null |
> 
> Indexes:
> 
>      "audit_param_pk" PRIMARY KEY, btree (audit_id)
> 
> sd_tems_partition_test=#
> 
> sd_tems_partition_test=# Alter table audit_logging.audit_param add 
> constraint audit_param_audit_fk2 FOREIGN KEY (audit_id) REFERENCES 
> audit_logging.audit_p(id);
> 
> *ERROR:  there is no unique constraint matching given keys for 
> referenced table "audit_p"*

The PK(UNIQUE constraint) on audit_logging.audit_p is:

"audit_pk1" PRIMARY KEY, btree (id, create_dtt)

You are only specifying id:

REFERENCES audit_logging.audit_p(id)
> 
> sd_tems_partition_test=#
> 
> Thanks,
> 
> Shatamjeev
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"
Next
From: Shatamjeev Dewan
Date:
Subject: RE: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"