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.
Source: A Relational Model of Data for Large Shared Data Banks
Codd retained that term. (Same source.)
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.