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.
PK FK composed, need references all fields if I’m not mistaken.
CONSTRAINT fk_table1 FOREIGN KEY (id, version) REFERENCES (table1)
– rbz
@This will not work because in
table2
only has a field referring totable1
. And if instead of putting the columnversion
as part of the primary key does not create the primary key only with theid
and then aUNIQUE CONSTRAINT
with both?– João Martins
@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.– Fábio Fischer
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).
– João Martins
Opa:
CONSTRAINT fk_table1 FOREIGN KEY (id, version) REFERENCES table1(id, version)
– rbz
@Joãomartins imagined =/. It seems to be a modeling problem, so.
– Fábio Fischer
I think I’m getting it wrong, so I’m reading the comments! rs...
– rbz
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 thetable1
and the composite key structure in the table "main"– rLinhares
@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 =)
– Fábio Fischer
The idea of @rLinhares is good! Create a field dummy only to be the primary key and then create a
UNIQUE CONSTRAINT
with theid
andversion
, thus ensured that the composition of the two columns was unique and already had a single primary key also that could correctly reference in thetable2
!– João Martins
@rLinhares understood and liked the idea. I’ll take a test and update the thread
– Fábio Fischer
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
– Rovann Linhalis
@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)
– rLinhares
"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
– Rovann Linhalis
@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...
– rLinhares
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.
– anonimo