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

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(dot)klaver(at)aklaver(dot)com