I know we can enforce this directly in the database, but we'd need some error checking code added to catch the db error, so I'm going to implement this directly in LightSwitch.
Here's a way to do this :-
Create a query under your table within the datasource (Just reading the your proposed unique key, and the primary key of the table), and filtering by a parameter equaling your proposed unique key).As below ....
Now we do the following :- 1. Go back to the table and open it in the designer.
2. And find the column to apply the unique key to, and select it,
3. Make sure in the properties window, that Is Searchable is checked, then scroll to the bottom of the properties window, and click 'Custom Validation'.
You should now be presented with a code behind screen populated with something like this...
OK, so we want to retrieve the results of our new query, and pass the currently loaded customer's customercode.
Then we can loop through each returned result (there SHOULD only be 1 result to loop through) but hey this isn't necessarily our database to work with, so lets use each one ... if we find two then we will report 2 errors!!
If our current id field doesn't match the ID field that we found in the database for this customer code, then the record must already exist in another record, so add a validation error.
If it does match, then we are simply updating the current record, allow this.
The code to do this is below...
I can think of more elegant solutions (like a UNIQUE checkbox in the designer!) but this will work, and will also allow you to enforce more complex business logic solutions (like checking more than one table for a record).
Here's an existing customer.... and what happens if I try and overwrite it with another record.
Voila ! Our CustomerCode column is now effectively a UNIQUE key that can be used in our business logic.
If anyone knows of a faster or simpler way to do this without hitting the DB directly, please let me know.
