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?
@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?
@veronica (There "may" be a solution with one table, I just don’t see it and it seems much easier to tackle with two tables, hence the question)
@immae Since the object represented by the ID can be renamed, which triggers an INSERT with a new version rather than an UPDATE, joint tables get a little messy, but yeah, it's my last resort solution.
@veronica So you want that no two objects can have the same name, which seems reasonnable. But also with your hypothetical constraint, if you have two objects named 'a' and 'b', when you rename 'a' to 'c', then you cannot rename 'b' to 'a' (since otherwise your constraint would fail), is that reasonnable too?
@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.