Persistence entity with composite key

composite-typesprimary-key

I have class named 'User'. It has the following properties

firstname
lastname
mobile
email
pin

I want to design a table or hibernate entity that will have unique email for each row; the same for mobile. One of email or mobile will be allowed to be null. I should be able to fetch a record by using email or mobile independently.

Best Answer

Some RDBMS will allow you to design a table's data column as unique but not required and some will not. Supposing it was allowed then you would simply write some code to first query the table on the most commonly entered field and if not found, on the least commonly entered. Job done.

Otherwise, you may need to do something like creating a unique filtered index as mentioned in this Stack Overflow answer: https://stackoverflow.com/questions/3570805/unique-non-required-column-in-sql-server-2008