ERROR: there is no unique constraint matching given keys for referenced table "audit_p"

[Shatamjeev Dewan]

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"
sd_tems_partition_test=#

Thanks,
Shatamjeev