1

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Uttam Gupta
  • 418
  • 2
  • 10

1 Answers1

3

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.

Milney
  • 6,253
  • 2
  • 19
  • 33
  • "*Also by default many RDMSes will create the clustered index based on the primary key*" no, that's not true. Postgres, Oracle, DB2, Firebird will not create a clustered index on the PK (because either they don't have clustered indexes or because it's unusual to use them in those DBMS) –  Aug 07 '20 at 08:47
  • @a_horse_with_no_name Yeah that's why I said *many* and not *all* :P – Milney Aug 07 '20 at 09:15
  • My point was: it's only a _few_ (I think it's only SQL Server and MySQL) not "many". –  Aug 07 '20 at 09:27