Key Difference between Primary Key and Unique key
- There can be one primary key in a table, while there can be multiple unique keys in the table.
- The purpose of the primary key is to enforce entity integrity; on the other hand, the purpose of unique key is to enforce unique data.
- In primary key, default Index is clustered, whereas in unique key, default index is not-clustered
- Primary key does not allow null columns, whereas the Unique key allows null columns.
- In the primary key, duplicate keys are not allowed, while in a unique key, if one or more key parts are null, then duplicate keys are allowed.
A primary key constrain is a column or group of columns in a table that uniquely identifies every row in that table. The Primary key can’t be a duplicate, meaning the same value can’t appear more than once in the table.
A table can have only one primary key. Primary key can be defined at the column or the table level. If you create a composite primary key, it should be defined at the table level.
A unique key is a group of one or more than one fields or columns of a table which uniquely identify database record.
A unique key is the same as a primary key, but it can accept one null value for a table column. It also cannot contain identical values. Unique constraints are referenced by the foreign key of other tables.
Here are the important differences between primary key and unique key:
|Primary Key||Unique Key|
|There can be one primary key in a table||There can be multiple unique keys in the table|
|It does not allow null columns.||It allows null columns.|
|Default Index is clustered||Default Index is no-clustered|
|The purpose of the primary key is to enforce entity integrity.||The purpose of unique key is to enforce unique data.|
|Primary key can be created using syntax:
CREATE TABLE Employee ( ID int PRIMARY KEY, Name varchar(255), City varchar(150) )
|Unique key can be created using syntax:
CREATE TABLE Employee ( ID int UNIQUE. Name varchar(255) NOT NULL. City varchar(150) )
|It is SQL constraint which allows you to uniquely identify each record or row in the database table.||It is SQL constraint that doesnot allow the same value tobe assigned to two isolatedRecords in a database table.|
|In the primary key, duplicate keys are not allowed.||In a unique key, if one or more key parts are null, then duplicate keys are allowed.|
Here are the important reasons to use primary key:
- The main aim of the primary key is to identify each and every record in the database table.
- You can use a primary key when you do not allow someone to enter null values.
- If you delete or update a record, the action you specified will be undertaken to make sure database data integrity.
- Perform restrict operation to rejects delete or update operation for the parent table.
- Data are organized in sequence of clustered index whenever you physically organize DBMS table.
Here are the important reasons to use unique key:
- The purpose of a unique key is to make sure that information in the column for each table record is unique.
- When you allow the user to enter the null value.
- Unique key is used because it creates a non-clustered index by default.
- Unique key can be used when you have to keep null values in column.
- When one or more than one field/columns of a table that uniquely identify a record in a database table.
Here, are the important features of primary key:
- The primary key implements the entity integrity of the table.
- You can keep only one primary in the table.
- The primary key contains of one or more table columns.
- Columns are defined as not null.
Here, are the important features of unique key:
- You can define more than one unique key in the table.
- By default, unique Keys are in non-clustered unique indexes.
- It constitutes of one or more table columns.
- The table column can be null, but only one null per column is preferable.
- A unique constraint can be easily referenced by a foreign key constraint.
The following example describes that there is a table called student. It contains five attributes, 1) StudID, 2) Roll No, 3) First Name, 4) Last Name, and 5) Email.
The Roll No attribute can never contain a duplicate or null value. It is because every student enrolled in a university can have unique roll number. You can easily identify each row of a table with student’s roll number. So it is considered as a primary key.
Consider the same student table with attributes, 1) StudID, 2) Roll No, 3) First Name, 4) Last Name, and 5) Email.
Stud ID can have a unique constraint where entries in Stud ID column can be unique because each student of a university must have a unique ID number. In case, if student is changing the university, in that case, he or she would not have any stud ID. The entry may have a null value as only one null is allowed in the unique key constraint.
- Unique key is better when you have columns you know shouldn’t contain duplication. This becomes a good way to make sure data validation.
- Primary key is ideal when you cannot keep null in the table. It can also use when you have a foreign key in another table for creating a relationship.