CODEWITHSUNDEEP
sqlpostgresql
Paddy
Paddy

Reputation: 2873

How to make a row's value unique across two columns?

Suppose I have two columns, Column A and Column B. Column A is required, and Column B is optional. If Column B is specified, I'd like to make sure that its value is not found in Column B (easy) OR Column A (seems much harder) outside its own row. Likewise, if Column A is changed, I'd like to make sure that its new value is not found in Column B or Column A, outside its own row.

So far, the closest I've gotten is the exclusion constraint, but that doesn't seem capable of comparing Column B to Column A. Is this possible outside the application layer, or am I stuck with an application layer solution?

The use case is that I'd like the following:

  1. Users always have usernames, which are always unique amongst all usernames and emails.
  2. Users sometimes have emails, which are always unique amongst all usernames and emails.

This allows me to know that, for example, if a user logs in with their email address I know which username is theirs. But I also want users to be able to use email addresses as usernames. And more confusingly, I'd like users to be able to specify one email address as a username and a separate email address as the email address on file.

Any pointers?

Upvotes: 2

Views: 173

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I think you should have one table for users. This should have a unique, auto incremented user id. You should have another table for logins. This would have columns such as:

  • UserId
  • LoginType (email or userid)
  • Name

This table has a unique constraint on name. In addition, it has a unique constraint on UserId and LoginType to ensure at most one value of each for any given user. You can add additional constraints to ensure that emails really look like emails, for instance.

If you want a separate email associated with the UserId for contact purposes, you can put that in the Users table.

The key idea: move the confusing notion of a login to a separate entity (table) in the database.

Upvotes: 2

Related Questions