What's the difference between a primary key and a unique key?

[chinmay]

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key does not allow NULLs, but unique key allows one NULL only.

[ketul]

Primary Key:

• Can be only one in a table
• In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
• Primary Key is a unique key identifier of the record

Unique Key:

• Can be more than one unique key in one table
• Unique key can have null values
• It can be a candidate key
• Unique key can be null and may not be unique

[traveloweb]

Primary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry). By default SQL-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key

[dpkmlm]

rimary Key is used to identify a row (record) in a table, whereas Unique-key is to prevent duplicate values in a column (with the exception of a null entry).

[companyformation]

Primary key is an index that helps locate table rows easily, however, primary key is also unique. but a table can only have one primary key which is incremental by default. unique key is just as it sounds, it must be unique, no duplication of data. example is making the email column of your database unique. you can have more unique keys in your table.

[aanchalkaura]

Both PRIMARY KEY and UNIQUE KEY enforces the Uniqueness of the values (i.e. avoids duplicate values) on the column[s] on which it is defined. These are the some difference between Primary and unique key:


PRIMARY KEY:


It doesn’t allow Null values.
By default it adds a clustered index
A table can have only one PRIMARY KEY Column[s]




UNIQUE KEY


Allows Null value. But only one Null
By default it adds a UNIQUE non-clustered index
A table can have more than one UNIQUE Key Column[s]

[paul12]

Primary Key:
There can only be one primary key in a table
In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
Primary Key is a unique key identifier of the record

Unique Key:
Can be more than one unique key in one table
Unique key can have NULL values
It can be a candidate key
Unique key can be NULL and may not be unique