PostgreSQL - Partial Foreign Key of Composite Primary Key

Question

PostgreSQL - Partial Foreign Key of Composite Primary Key

Navigation

#1 by (2 votes)

4

Talk to people,

I need to create a structure template in which all my tables have a primary key consisting of id and version, so I can have multiple versions for each id.

Problem:

CREATE TABLE table1 {
    id              BIGSERIAL,
    version         INT NOT NULL,
    CONSTRAINT pk_table1 PRIMARY KEY (id, version)
}

CREATE TABLE table2 {
    id              BIGSERIAL,
    version         INT NOT NULL,
    table1_id       BIGINT NOT NULL,
    CONSTRAINT pk_table2 PRIMARY KEY (id, version),
    CONSTRAINT fk_table1 FOREIGN KEY (table1_id) REFERENCES table1(id)
}

When executing the above structure, by not referencing all fields of the composite primary key of table1 in FK, the following error is returned:

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

My question is in relationship with other tables. I would like to not need to create a version field for every FK I create in the table to simplify the number of fields, be able to keep the relationship of records only by id, and track the history of the relationship by date fields. >

I've read Postgre documentation about the MATCH PARTIAL clause I believe which would be my solution, but has not yet been implemented.

Is there any work around for this situation?
What am I doing against some principle of data modeling? Should I run into a different solution?

I'm using PostgreSQL version 10.5.

sql modelagem postgresql foreign-key

asked by anonymous 30.08.2018 / 15:04

1 answer

Trajectory of an object using canvas in javascript Update cart status without leaving the page | Woocommerce 3.4+

score 2 · Accepted Answer

Given all the comments, and especially the @rLines indication, will this solve your problem ?

CREATE TABLE table1 
{
    dummy_id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id              BIGSERIAL   NOT NULL,
    version         INT         NOT NULL,
    UNIQUE(id, version)
}

CREATE TABLE table2
{
    dummy_id        INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id              BIGSERIAL   NOT NULL,
    version         INT         NOT NULL,
    table1_id       BIGINT      NOT NULL,
    CONSTRAINT      fk_table1   FOREIGN KEY (table1_id) REFERENCES table1(dummy_id),
    UNIQUE(id, version)
}