|
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 |
