I'm kind of stumped by a seemingly simple problem in Db design, but the more I think about about it, the more it twists my mind.
Let's say I have columns C1 and C2 in a table. How do I enforce that the pairing of values of C1 and C2 is pairwise disticnt? That is, a specific composite value for C1 and C2, say X and Y, can appear any number of times in the table, but once X/Y appears, you cannot have X/Z, or W/Y.
It seems such a simple constraint to model, but several hours of research still leaves with no solutions. I'm using PostgreSQL, but I guess the issue is probably db-agnostic. I explored partial indexes, but that feature seems too limited for my problem. Any ideas?
To put this in more context, my problem arises from trying to design a users table that maintains version history with the following columns:
- internal_id (primary key, auto-generated number),
- external_id (varchar, not null),
- auth_id (varchar, not null, can be phone #, email address, etc),
- display_name (varchar),
- access_level (number, not null),
- when_record_version_created (timestamp, not null),
- when_record_version_expired (timestamp),
- unique (external_id, when_record_version_created)
A record is valid from when_record_version_created to when_record_version_expired. This should basically work, but without enforcing the pairwise distinct relationship between external_id and auth_id, it would be possible to associate an auth_id with more than one external_id, which would corrupt the data.
The problem can also be expressed as: how do I setup a unique constraint on a column, where the uniqueness only applies within groups defined by distinct values of another column in the same table.