Postgresql - Partial Foreign Key of Composite Primary Key

Asked

Viewed 2,433 times

4

Fala galera,

I need to create a structure model where all my tables have primary key composed by 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 the primary key fields composed of the table1 in FK, the following error is returned:

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

My doubt is in the relationship with other tables. I would like to not need to create a version field for each FK I create in the table to simplify the amount of fields, manage to maintain the record relationship only by id and control the relationship history by date/time fields.

I’ve already read the documentation of Postgre on the clause MATCH PARTIAL which I believe would be my solution, but has not yet been implemented.

  • Is there any Around work for this situation?
  • What am I doing against some data modeling principle? Should I run to a different solution?

I’m using Postgresql version 10.5.

sql postgresql modeling foreign-key
  • 1

    PK FK composed, need references all fields if I’m not mistaken. CONSTRAINT fk_table1 FOREIGN KEY (id, version) REFERENCES (table1)

  • 1

    @This will not work because in table2 only has a field referring to table1. And if instead of putting the column version as part of the primary key does not create the primary key only with the id and then a UNIQUE CONSTRAINT with both?

  • @Joãomartins, if I only left the id as PK, it would have to be unique. I would like to keep a version record for each id.

  • 1

    Then you won’t get what you want. FK always needs to reference a specific column and not a composite key (which could be multiple columns).

  • 1

    Opa: CONSTRAINT fk_table1 FOREIGN KEY (id, version) REFERENCES table1(id, version)

  • @Joãomartins imagined =/. It seems to be a modeling problem, so.

  • 1

    I think I’m getting it wrong, so I’m reading the comments! rs...

  • 1

    I understand that the ideal is for you to have a field that is identity (whether or not primary key) to reference in the table2, so you keep the 1-to-1 relationship with the table1 and the composite key structure in the table "main"

  • @Rbz hehehe think so. I know how I refer composite key, but I wish I didn’t need to inform all fields of PK at FK. But I appreciate the interest =)

  • 2

    The idea of @rLinhares is good! Create a field dummy only to be the primary key and then create a UNIQUE CONSTRAINT with the id and version, thus ensured that the composition of the two columns was unique and already had a single primary key also that could correctly reference in the table2!

  • 1

    @rLinhares understood and liked the idea. I’ll take a test and update the thread

  • 1

    seems to me a problem X,Y... if you explain your real problem, we can propose an ideal solution... have a record and in the same table your versioning seems to me very strange

  • 1

    @Rovannlinhalis think that what the idea of a key solves the problem in question (which perhaps even characterizes an XY problem), but I understand the point raised about "how to use part of a composite key" be valid (at least I was intrigued about the possibility =p)

  • 1

    "how to use part of a composite key"no longer makes sense to me, the foreign key would reference more than one record in the foreign table... the question of MATCH PARTIAL, I understand that concerns foreign keys (or part) can be null or not, see MATCH FULL and MATCH SIMPLE... ie, in no case could the key refer to more than one record in the source table. About the problem in question, it would be better for the AP to define what it wants to control versions and these should be in a third table, but only it explaining to be sure

  • 2

    @Rovannlinhalis got it, and I think it makes sense. I was not thinking of part of a composite key as being the primary key of another table, but rather it could be repeated...

  • If it makes sense to use only the id field in your template then create a table with only this field and another table with this + version field and use each of them when appropriate.

Show 11 more comments

1 answer

2


considering all comments, and in particular the indication of @rhubarb, 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)
}

Browser other questions tagged sql postgresql modeling foreign-key

You are not signed in. Login or sign up in order to post.