Take the 2-minute tour ×
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's 100% free, no registration required.

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 ?

share|improve this question
    
Which RDBMS are you using? –  Thomas Stringer Mar 15 '13 at 13:02
    
Primary key requires fields in the key to be NOT NULL also? –  Yasir Arsanukaev Mar 15 '13 at 13:22
1  
I don''t think the question is about differences between Primary and Unique constraints. But whether - and since there is not much difference between them - why do we have both. Why do we need both types. –  ypercube Mar 15 '13 at 13:25
add comment

4 Answers

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.

share|improve this answer
    
+1 to Catcall. Well said. And I agree with your last statement on UNIQUE index vs constraint. Interestingly enough some RDBMS's like DB2 treat them the same under the covers. A UNIQUE constraint will actually create a UNIQUE index under the covers (so will the primary key since it is a UNIQUE constraint). And similarly a UNIQUE index enforces a UNIQUE constraint. Again, that's just DB2. Other DBMS's may handle these differently. But I do agree with Catcall's answer. –  Chris Aldrich Mar 15 '13 at 17:29
    
I think most platforms nowadays implement a unique constraint with a unique index. I think I've seen at least one system that let you drop a unique index even if a foreign key referenced it. I could be wrong. Would have been within the last 10 years or so. –  Mike Sherrill 'Cat Recall' Mar 15 '13 at 19:38
add comment

Every table needs a way to uniquely identify a record - that is the purpose of the primary key. A primay key cannot contain nuls 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 uniquenly identified. You do not create a PK just for joins, they are needd 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 uniwue and immutable (that is they shoudl 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 mean 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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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:

  • There can only be one declared on each table
  • All the columns in the primary key must be not null

Whereas 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.