Sql-server – PRIMARY KEY CONSTRAINT fundamentals

database-designprimary-keysql server

Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ?

The RDBMS that is used is SQL Server.

In fact, after creating a unique index (not null), I can create a foreign key constraint on an other table pointing to the column that is indexed without having to declare that this column is "the primary key" … so, what is the purpose of it ? Is it a question of esthetics ?

Best Answer

The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data.

The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . .

Source: A Relational Model of Data for Large Shared Data Banks

Codd retained that term. (Same source.)

Normally, one domain (or combination of domains) of a given relation has values which uniquely identify each element (n-tuple) of that relation. Such a domain (or combination) is called a primary key.

It's not unusual for a relation to have more than one "combination of domains" that uniquely identify each tuple. The relational model offers no theoretical basis for elevating the status of one of them to "primary". As far as the relational model is concerned, all candidate keys are created equal.

As far as standard SQL is concerned, any column or combination of columns that's declared UNIQUE can be the target of a foreign key constraint. In the past, dbms platforms differed slightly in how they handled NULL in a column declared UNIQUE. That's part of the reason old-school database designers lean toward NOT NULL UNIQUE declarations; the other part is that NOT NULL UNIQUE is functionally equivalent to PRIMARY KEY.

There is a semantic difference between a UNIQUE index and a UNIQUE constraint. I prefer to see constraints expressed as constraints.