ERROR: there is no unique constraint matching given keys for referenced table "audit_p"

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.

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

Thanks a lot Michael. Get well soon.

From: Michael Lewis <mlewis@entrata.com>
Sent: November-18-19 12:57 PM
To: Shatamjeev Dewan <sdewan@nbsps.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: ERROR: there is no unique constraint matching given keys for referenced table "audit_p"

On Mon, Nov 18, 2019 at 10:10 AM Shatamjeev Dewan <sdewan@nbsps.com<mailto: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.

I was under the impression that PostgreSQL 12 removed this limitation. Was
this incorrect?
https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/

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

On Mon, Nov 18, 2019 at 1:01 PM Miles Elam <miles.elam@productops.com>
wrote:

I was under the impression that PostgreSQL 12 removed this limitation. Was
this incorrect?

https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/

Yeah, as the comments in that article make clear, the new feature has some
rough edges (limitations) that you are hitting with your desired model.

David J.