Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 ?
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.
Every table needs a way to uniquely identify a record - that is the purpose of the primary key. A primary key cannot contain nulls because that would mean you could not uniquely identify a record with it. Part of the reason for defining the PK is to make sure your table is designed in such a way that each record can be uniquely identified. You do not create a PK just for joins, they are needed even on tables that will never be joined to. And joins as you noted do not require PKs because joining is not the purpose of a PK. They are needed to make sure the database can correctly find a record to update or delete it. Primary keys should be unique and immutable (that is they should not be subject to change 99.9% of the time).
However, it is not always only the primary key that needs uniqueness. This is especially true when you use surrogate keys. So unique indexes are also needed beyond primary keys becaue you might might want to use a surrogate key because it is faster for joins and it means you won't have to update the company name a million times through various child tables when the name changes. However you still want the natural key to be unique. There are other times when you need unique values as well. I might want to make sure I don't have multiples of some other identifier besides the one I use for the PK. Some things you model simply have multiple things that should be unique.
You can speed up a join using an index (whether or not that index is unique).
A primary key constraint enforces uniquness of an index and tells the DBMS what a foreign key points at.
The fact that a primary key constraint often results in a unique index doesn't mean that one thing is equivalent to (or can stand in for) the other.
Constraints are declarative. They make a statement about how your schema is structured. On the other hand, a select query is procedural - especially if you are going to use query hints to tell the DBMS which indexes to use in joining two tables.
The DBMS uses a constraint definition for different purposes than it uses an index, even if for practical reasons the constraint definition and the index are linked.
I believe the important difference is to inform developers which of the available unique keys should be used when applying foreign keys to a table.
The important features of a primary key are:
not nullWhereas you can have multiple unique keys in a table and the referenced columns may allow null. By declaring a primary key, you are saying "this is the candidate/surrogate key that should be referenced by foreign keys". This is well understood by database developers who are unlikely to apply a foreign key to a unique constraint when a primary key is available.
Unique constraints can also be used to enforce rules such as "a customer can only have one default contact number". A primary key can't be used for this purpose, as customers may have multiple non-default numbers, so the constraint can't uniquely identify all rows in the table.
