Posted on 2012-03-14
I had a look at the Northwind database and found that I could add a new "Product" row having the same "ProductName". Is this how the table is supposed to work? The only uniqueness is on the identity column, which of cause is the primary key.
One would think that the "ProductName" should be unique, or am I not understanding the roles of these two columns correctly.
As far as I can see though, a mistake can be made if a data entry clerk captures a new row but a duplicate "ProductName". This would probably not be detected since the primary key is the identity column.
Is my reasoning correct, if so, then should I design a database making the "ProductName" column a primary key column?
When is using an identity column as the primary key not okay?