taaSarge on Wed, 19 Dec 2012 19:04:14

I have a database where many of the tables have a unique clustered index on the Table_ID (identity column) BUT there is no Primary Key defined.

Is there a good reason(s) why I should build primary keys for those tables.  Note, the Primary Key would be same Table_Id as the clustered index.



Uri Dimant on Thu, 20 Dec 2012 06:11:34

Yes it is, as there will be fk referencing to the pk and when you perform a join that will improve performance. Note,  your PK could be unique NCI if you know the better candidate for CI.

Kalman Toth on Thu, 20 Dec 2012 07:51:12

You should have PRIMARY KEYs, UNIQUE KEYs and FOREIGN KEYs in the database.

Without FOREIGN KEYs you don't have a database, you just have isolated tables.

Simple database schema:

Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Server 2012 Programming

Varsha Mishra on Thu, 20 Dec 2012 11:47:29

Do these columns support NULL values? If yes, you need a unique clustered index to be defined instead of a primary key.

Dan Guzman on Thu, 20 Dec 2012 12:20:45

A primary key, unique constraint and unique index provide similar functionality.  All guarantee no duplicate keys are permitted and allow referencing foreign keys.  As mentioned, the big difference is that primary keys do not allow nulls.

Keep in mind that constraints are logical constructs that declare data integrity requirements whereas indexes are physical elements that improve performance.  SQL Server automatically creates an index on primary and unique constraints keys as part of the physical implementation so you don’t need to create separate indexes on those.  You have full control over whether the constraint indexes are clustered or not.

In the relational model, tables should have primary keys, unique constraints and foreign keys as appropriate.  But SQL Server does not enforce this, which allows you to use the database as a data store for non-relational data.

Database Experts on Mon, 24 Dec 2012 12:54:12

When you are not doing Pk-FK Funtionality and manage database objects relationship by own way,but when you do replication that time you'll definitely face the issue.because that table will not be replicated.

Kalman Toth on Mon, 31 Dec 2012 09:50:10

>But SQL Server does not enforce this, which allows you to use the database as a data store for non-relational data.

That also allows relational data to be stored in poorly designed tables.

Jayanth Kurup on Wed, 02 Jan 2013 13:29:20

Ideally when designing a table it should have a PK , in datawarehousing and some other cases you will see that a PK is not a must. e.g the fact table tables with many to many realtionships

Also even if you do not enfore the PK, you can simulate it by using the inner join within the stored procedure and by using triggers etc

I have seen both implmentations and if you want a bug free and well designed system use the PK.