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