I have searched everywhere for this and it seems like there isn't any obvious solution. I have a website with a lot of users but I need to store a globally unique profile value (phone) nobody can use the same phone number twice.
It would be very slow to loop through all my users get their phone values and match them against the user provided one to reject it.
Is there a way to make a profile value unique to all users? Just like a username is unique or a email? Thanks.
Here's how you create an index in a table in SQL:
CREATE INDEX IDX_CUSTOMER_PHONE on user (phone)
When you query the database, it'll look up your value by quickly searching the table for the phone number, rather than looping through all values:
SELECT phone FROM user WHERE phone = '555-555-5555'
If you need help with the implementation in your C# code, it will require you to query the database. My suggestion would be to write your stored procedure such that it returns 2 result sets. The first result set would be the record itself and the second result set would return true or false whether it errored out during the save (on create or update). In your C# code, check the errors result set first, and display duplicate if it returned a result set.