Difference between UNIQUE and PRIMARY key

MySQL Primary Keys

In MySQL, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a NULL value. A table can have only one primary key.

In MySQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement.

You use the ALTER TABLE statement in MySQL to drop, disable or enable a primary key.

MySQL Unique Key

A unique key in MySQL is a single field or combination of fields that ensure all values going to store into the column will be unique. It means a column cannot stores duplicate values. For example, the email addresses and roll numbers of students in the "student_info" table or contact number of employees in the "Employee" table should be unique.

MySQL allows us to use more than one column with UNIQUE constraint in a table. It can accept a null value, but MySQL allowed only one null value per column. It ensures the integrity of the column or group of columns to store different values into a table.

Needs of Unique Key

It is useful in preventing the two records from storing identical values into the column.

It stores only distinct values that maintain the integrity and reliability of the database for accessing the information in an organized way.

It also works with a foreign key in preserving the uniqueness of a table.

It can contain null value into the table.

Difference between Primary Key and Unique Key

Primary Key

Unique identifier for rows of a table

Cannot be NULL

Only one primary key can be present in a table

present in a table

Selection using primary key creates clustered index

Unique Key

Unique identifier for rows of a table when primary key is not present

Can be NULL

Multiple Unique Keys can be present in a table

present in a table

Selection using unique key creates non-clustered index

The syntax to create a primary key using the CREATE TABLE statement in MySQL is:

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

  last_name VARCHAR(30) NOT NULL,

  first_name VARCHAR(25),

  birthday DATE,

  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)

);

Create Primary Key - Using ALTER TABLE statement

ALTER TABLE contacts

  ADD CONSTRAINT contacts_pk 

    PRIMARY KEY (contact_id);

The following syntax is used to create a unique key in MySQL.

CREATE TABLE table_name(  

    col1 datatype,  

    col2 datatype UNIQUE,  

    ...  

);  

for creating more than one Unique Key

CREATE TABLE table_name(  

  col1 col_definition,  

  col2 col_definition,  

  ...  

  [CONSTRAINT constraint_name]  

  UNIQUE(column_name(s))  

);