Ask HN: Constraint to enforce pairwise distinct values in two columns in table
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 provides no clear solutions. I'm using PostgreSQL, but I guess the issue is db-agnostic. I explored partial indexes, but that feature seems too limited for this 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 (auto-generated integer, primary key),
- external_id (varchar, not null),
- auth_id (varchar, not null, phone #, email address, etc),
- display_name (varchar),
- access_level (integer, not null),
- record_version_number (integer),
- record_version_created_by (varchar, not null),
- when_record_version_created (timestampz, not null),
- when_record_version_expired (timestampz),
- unique (external_id, record_version_number)
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 active auth_id with more than one external_id, which would corrupt the data.