The pragmatists have won the argument of surrogate vs. natural primary keys in favor of surrogate keys*. In my own work I always use SQL Server identity columns without a second thought. But it occurs to me that, for a table to be in 1st normal form, I should be able to identify a natural key and enforce it with a unique constraint. I can't do this for all the tables in my database, so my database doesn't even meet the lowest criteria of normalization.
Do you agree that a table with a surrogate primary key must also have a unique constraint on a natural key in order to be in 1NF?
*I think Joe Celko is still fighting the good fight, see the last paragraph.
Edited to add:
Thanks for the responses. My impression is that adding a unique constraint is not a common practice, so I'm somewhat surprised that the responses so far have been unanimous.
Best Answer
Yes!
If the table is supposed to record at most instance of the natural key, you need a constraint on the relevant column(s) to enforce that. Otherwise, you can end up with a table of 50,000,000 rows, each with a different ID value but otherwise identical -- which is pathetic or ludicrous or a travesty, depending on your viewpoint.