Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux