From: | Shatamjeev Dewan <sdewan(at)nbsps(dot)com> |
---|---|
To: | Michael Lewis <mlewis(at)entrata(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | ERROR: there is no unique constraint matching given keys for referenced table "audit_p" |
Date: | 2019-11-18 17:10:43 |
Message-ID: | F2F23E564EB92A4C80228EB86713BA130BF984@TO1RODC.nbspaymentsolutions.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Lewis | 2019-11-18 17:57:18 | Re: ERROR: there is no unique constraint matching given keys for referenced table "audit_p" |
Previous Message | Javier Ayres | 2019-11-18 13:01:04 | Re: Weird ranking results with ts_rank |