What is the difference between a primary key and a unique constraint?

Question

Someone asked me this question on an interview...

Answer 1

A primary key is a unique field on a table but it is special in that the table considers that row its key. That means that other tables can use this field to create foreign key relationships to themselves.

A unique constraint simply means that a particular field must be unique.

Answer 2

Every primary key is a unique constraint, but in addition to the PK, a table can have additional unique constraints.

Say you have a table Employees, PK EmployeeID. You can add a unique constraint on SSN, for example.

Answer 3

A primary key is a minimal set of columns such that any two records with identical values in those columns have identical values in all columns. Note that a primary key can consist of multiple columns.

A uniqueness constraint is exactly what it sounds like.

Answer 4

In addition to Andrew's answer, you can only have one primary key per table but you can have many unique constraints.

Answer 5

1. Primary key's purpose is to uniquely identify a row in a table. Unique constraint ensures that a field's value is unique among the rows in table.
2. You can have only one primary key per table. You can have more than one unique constraint per table.

Answer 6

Primary keys can't be null. Unique keys can.

Answer 7

Primary key can't be null but unique constraint is nullable. when you choose a primary key for your table it's atomatically Index that field.

Answer 8

Primary keys are essentially combination of (unique +not null). also when referencing a foreign key the rdbms requires Primary key.

Unique key just imposes uniqueness of the column.A value of field can be NULL in case of uniqe key. Also it cannot be used to reference a foreign key, that is quite obvious as u can have null values in it

Answer 9

There are several good answers in here so far. In addition to the fact that a primary key can't be null, is a unique constraint itself, and can be comprised of multiple columns, there are deeper meanings that depend on the database server you are using.

I am a SQL Server user, so a primary key has a more specific meaning to me. In SQL Server, by default, primary keys are also part of what is called the "clustered index". A clustered index defines the actual ordering of data pages for that particular table, which means that the primary key ordering matches the physical order of rows on disk.

I know that one, possibly more, of MySql's table formats also support clustered indexing, which means the same thing as it does in SQL Server...it defines the physical row ordering on disk.

Oracle provides something called Index Organized Tables, which order the rows on disk by the primary key.

I am not very familiar with DB2, however I think a clustered index means the rows on disk are stored in the same order as a separate index. I don't know if the clustered index must match the primary key, or if it can be a distinct index.

Answer 10

Both guarantee uniqueness across the rows in the table, with the exception of nulls as mentioned in some of the other answers.

Additionally, a primary key "comes with" an index, which could be either clustered or non-clustered.

Answer 11

1. Primary key can not be null but unique can have only one null value.
2. Primary key create the cluster index automatically but unique key not.
3. A table can have only one primary key but unique key more than one.

Answer 12

A great number of the answers here have discussed the properties of PK vs unique constraints. But it is more important to understand the difference in concept.

A primary key is considered to be an identifier of the record in the database. So these will for example be referenced when creating foreign key references between tables. A primary key should therefore under normal circumstances never contain values that have any meaining in your domain (often automatically incremential fields are used for this).

A unique constraint is just a way of enforcing domain specific business rules in your database schema.

Becuase a PK it is an identifier for the record, you can never change the value of a primary key.