CODEWITHSUNDEEP
sqlsql-server
Uttam Gupta
Uttam Gupta

Reputation: 518

Why Primary key is required in SQL Server, if non nullable unique key can serve the purpose

Can an expert help me in understanding why we need primary key in a sql table, if the purpose of uniquely identifying a row can be served using non nullable unique key.

Upvotes: 1

Views: 51

Answers (1)

Milney
Milney

Reputation: 6417

A Primary Key isn't required per se. But it serves a different function conceptually than a unique index.

The primary key identifies a row. A unique index simply ensures there are not duplicates. SQL Engine can optimize queries based on this information. Also by default many RDMSes will create the clustered index based on the primary key.

You can only have one primary key, and the column(s) can't be nullable. You can have multiple unique indexes and they can include nullable columns.

If you wanted (although that would be a terrible design, so you shouldn't) you could have a table without a primary key, that had a unique index.

This is kindof a disconnect between Logical database modelling and Physical database design/implementation - logically the Entity (Table) should have a primary key that uniquely identifies each Instance (Row). In reality you are free to do what you want with your database system.

Upvotes: 3

Related Questions