Any database wizards out there?
I have a table with an ID column. The ID column is unique together with a valid date, so the ID itself is not unique.
However, I also need a name column that is unique under the condition that any given name can only correspond to one specific ID (but not necessarily the other way around).
The database is PostgreSQL. Is it possible to formulate, for instance, a UNIQUE INDEX to achieve this?
@veronica I’m not sure I am parsing correctly your question; you have a table with (at least) three columns id, date and name. You have the constraint that each tuple (id, date) is unique (but you may have duplicate ids, and duplicate dates). And you want to add a constraint such that for any "name" value, all the rows having that name have the same id?
@immae It's an implementation of this concept: https://docs.sqlalchemy.org/en/20/orm/examples.html#module-examples.versioned_rows
So the ID has a valid from and to date range. But we need a lookup by name that points to single ID for a given timestamp in history.
@veronica ok I think I get the use case. If I had to attack the issue I would probably work with two joint tables, is that an option?
