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
