Postgres Pro
Downloads
Home   >   mailing lists

Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p" - Mailing list pgsql-general

From Miles Elam
Subject Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"
Date
Msg-id CAALojA9wdnO0jraUyYY5UQP0cVEedNw_QJi0Rk+9FrodFwyv-A@mail.gmail.com
Whole thread Raw
In response to Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"  (Michael Lewis <mlewis@entrata.com>)
Responses Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
Tree view
I was under the impression that PostgreSQL 12 removed this limitation. Was this incorrect?

On Mon, Nov 18, 2019 at 9:58 AM Michael Lewis <mlewis@entrata.com> wrote:
On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan <sdewan@nbsps.com> wrote:

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"


As far as I understand, what you want is not possible. You cannot partition on a timestamp and then foreign key to an object_id natively. You can get around this with triggers- https://www.depesz.com/2018/10/31/foreign-key-to-partitioned-table-part-2/ - but that would not be recommended.

Admittedly, I have a pretty nasty head cold so it may be that someone chimes in with much better insight on your design.

By the way, there are strong recommendations for using timestamp WITH TIMEZONE in nearly all cases. I just wanted to mention since timestamp without timezone is rarely the best choice.

pgsql-general by date:

Previous
From: Dave Hughes
Date:
Subject: pgaudit log directory
Next
From: "David G. Johnston"
Date:
Subject: Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"