Eugene Elutin (10/24/2012)
Use surrogate Primary Key (the best and simple in in SQL server will be having INT IDENTITY PK, you don't need to add "firstname+lastname" to it). Use IndividualPersonalKey as another key which can be unique but null-able. It's quite common problem met by database designers and using surrogate keys does help to solve this problem in simple and elegant way.And...
Disregard J.C. comments if he will post here and suggest using fake ones with later update to the real ones - huge headache for no gains!
Can you be absolutely sure that whoever issue IndividualPersonalKey's will never decide to reuse ones?
+1 on the identity. This seems to be a perfect fit.
And as Eugene said, don't add the firstname+lastname to it. That will cause you nothing but grief. You will end up with a primary key containing a name that is no longer valid when somebody changes their name. This type of demographic data should not be considered part of a key because it can change.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/