Auto-generated primary key versus user defined primary key
i am designing a database using sql server 2008 r2. In around 90% of the tables i have added an identifier column that will contain an auto-generated number that can uniquely indentify the table columns.
But in some table i will have a unique value that the users can enter which can also uniquely indentify the table columns , for example i have a table named Document_Status which have Description column such as (new, draft, published, canceled,etc); now in the Document_status table no two columns can have the same Description values. so should I in this case set the Description column as the primary key and there will be no need to set an auto-generated ID field in the Document_status table.
It may help if you show your tables. What do you mean with "no two columns can have the same Description values" - perhaps it should be "no two rows"? But if that's the case, it sounds unlikely - can there only be one new document, one in draft, one cancelled, one published, etc., for all time?