Postgres Pro
Downloads
Home   >   mailing lists

Thread: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"

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

From
Shatamjeev Dewan
Date:

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

Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"

From
Michael Lewis
Date:
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.

Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"

From
Adrian Klaver
Date:
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

RE: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"

From
Shatamjeev Dewan
Date:

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

Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"

From
Miles Elam
Date:
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.

Re: ERROR: there is no unique constraint matching given keys forreferenced table "audit_p"

From
"David G. Johnston"
Date:
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?

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.