SQL - Unique Key

---

A KEY in Database Management Systems is one or a set of attributes/columns that are used to uniquely identify a tuple (or a record) in a table.

Unique Key in SQL

A SQL UNIQUE key constraint (or condition) does not allow duplicate values in a column of a table, i.e. it is used to uniquely identify a record in a table. It prevents two records from having same values in a column.

It is just an alternative to the primary key; as both unique and primary key constraints assure uniqueness in a column of the table.

Usually, any relational database contains a lot of information stored in multiple tables and each table holds a huge number of records. When we are handling such huge amounts of data there is a chance of redundancy (duplicate records). SQL keys are a way to handle this issue.

Suppose we have a table named CUSTOMERS to store the customer records in a Bank and if one of the column names is MOBILE_NO then, we can create a UNIQUE constraint on this column to prevent the entry of multiple records with the same mobile number.

Here are some key points of the UNIQUE KEY −

• The unique key is similar to the primary key in a table, but it can accept NULL values, whereas the primary key does not.

• It accepts only one NULL value.

• It cannot have duplicate values.

• It can also be used as a foreign key in another table.

• A table can have more than one Unique column.

Syntax

Following is the syntax to create a UNIQUE key constraint on a column in a table −

CREATE TABLE TABLE_NAME ( COLUMN1 UNIQUE, COLUMN2 datatype,….);

As you observe, we just need to specify the keyword “UNIQUE” after the name of the column while creating a table.

Example

Using the following SQL query, we are creating a table named CUSTOMERS with five fields ID, NAME, MOBILE_NO, ADHARCARD_ID, AGE, ADDRESS, and SALARY in it. Here, we are creating a UNIQUE KEY CONSTRAINT on the column MOBILE_NO.

CREATE TABLE CUSTOMERS ( 
   ID INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   MOBILE_NO BIGINT UNIQUE, 
   ADHARCARD_ID BIGINT, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY DECIMAL (18, 2));

Output

Following is the output of the above SQL statement −

(0 rows affected)

Verification

Since we have created a UNIQUE constraint on the column named MOBILE_NO, we cannot insert duplicate values in it.

First of all, let's insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS (ID, NAME, MOBILE_NO, ADHARCARD_ID, AGE, ADDRESS, SALARY) VALUES (1, 'Ramesh',9830542647,981234567821, 32, 'Ahmedabad', 2000.00 ); 

Now, let’s insert another record with the same Mobile number −

INSERT INTO CUSTOMERS (ID,NAME,MOBILE_NO, ADHARCARD_ID,AGE,ADDRESS,SALARY) VALUES (2, 'Ganesh',9830542647,991234367121, 40, 'Hyderabad', 2200.00 );

The above SQL insert query throws an error (duplicate entry) while inserting the record into the table. It says that the mobile number can not be duplicated as it is a UNIQUE KEY column of the CUSTOMERS table.

Violation of UNIQUE KEY constraint 'UQ__CUSTOMER__150F2766608F1072'. Cannot insert duplicate key in object 'dbo.CUSTOMERS'. The duplicate key value is (9830542647).

Multiple unique keys

We can create one or more UNIQUE KEY constraints on a column in a single SQL table.

Consider the above CUSTOMERS table has two columns named MOBILE_NO and ADHARCARD_ID. To avoid the duplication of mobile number and adharcard id, we can create a UNIQUE constraint on these columns.

Syntax

Following is the syntax to create unique key constraints on multiple columns in a table −

CREATE TABLE TABLE_NAME(COLUMN1 UNIQUE, COLUMN2 UNIQUE,…)

Example

Assume we have created a table with the name CUSTOMERS in the SQL database using CREATE TABLEstatement.

Here we are creating a UNIQUE constraint on columns MOBILE_NO and ADHARCARD_ID using the UNIQUE keyword as shown below −

CREATE TABLE CUSTOMERS ( 
   ID INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   MOBILE_NO BIGINT UNIQUE, 
   ADHARCARD_ID BIGINT UNIQUE, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY DECIMAL (18, 2)
);

Output

Following is the output of the above SQL statement −

(0 rows affected)

Verification

Since we have created a UNIQUE constraint on the column named MOBILE_NO and ADHARCARD_ID, we cannot insert duplicate values in it.

First of all, let’s insert a record into the CUSTOMERS table −

INSERT INTO CUSTOMERS 
(ID,NAME,MOBILE_NO, ADHARCARD_ID,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh',9830542647,981234567821, 32, 'Ahmedabad', 2000.00 );

Let’s insert another record with the same mobile number and the same adharcard id −

INSERT INTO CUSTOMERS (ID,NAME,MOBILE_NO, ADHARCARD_ID,AGE,ADDRESS,SALARY) VALUES (2, 'Ganesh',9830542647,758012203456, 40, 'Hyderabad', 2200.00 );

The following result verifies that UNIQUE constraints can be created on multiple columns in an SQL table, and each constraint prevents the insertion of duplicate records −

ERROR 1062 (23000): Duplicate entry '9830542647' for key 'customers.MOBILE_NO'
ERROR 1062 (23000): Duplicate entry '758012203456' for key'customers. ADHARCARD_ID'

Unique Key on an existing column

We can add a unique key constraint on an existing column of a table. For instance, we have created a table named Customers, it has many existing columns, and the column ADHARCARD_ID is one of them. Now let’s try to create a unique key constraint on this column.

Syntax

Following is the syntax to create a unique constraint on existing columns of a table −

ALTER TABLE TABLE_NAME ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (COLUMN_NAME);

Note − Here the UNIQUE_KEY_NAME is just the name of the UNIQUE KEY, It is optional to specify the name while creating an unique key. It is used to drop the CONSTRAINT from the column in a table.

Example

Using the ALTER TABLE statement, you can add a unique key constraint on any existing column in a table as shown below −

ALTER TABLE CUSTOMERS ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE(ADHARCARD_ID);

Output

Following is the output of the above statement −

Query OK, 0 rows affected (0.05 sec)

Dropping an Unique Key constraint

If we have already created a unique constraint on a column, you can drop it whenever not needed. To drop the Unique Constraint from the column of a table you need to use the ALTER TABLE statement.

Syntax

Following is the SQL query to drop the UNIQUE constraint from the column of a table −

ALTER TABLE TABLE_NAME DROP CONSTRAINT UNIQUE_KEY_NAME;

Example

Consider the CUSTOMERS table created above, we have created the UNIQUE constraints on two columns named MOBILE_NO and ADHARCARD_ID; let's drop the UNIQUE constraints from the column ADHARCARD_ID by executing the following SQL query −

ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_KEY_NAME;

Output

Following is the output of the above statement −

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let’s insert two duplicate records of column adharcard id −

INSERT INTO CUSTOMERS (ID,NAME,MOBILE_NO, ADHARCARD_ID,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh',9830542647,758012203456, 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,MOBILE_NO, ADHARCARD_ID,AGE,ADDRESS,SALARY) VALUES (2, 'Ganesh',9830542648,758012203456, 40, 'Hyderabad', 2200.00 );

If you verify the contents of the table, you can observe that both the records have the same adharcard id as shown below −

+----+--------+------------+--------------+-----+-----------+---------+
| ID | NAME   | MOBILE_NO  | ADHARCARD_ID | AGE | ADDRESS   | SALARY  |
+----+--------+------------+--------------+-----+-----------+---------+
|  1 | Ramesh | 9830542647 | 758012203456 |  32 | Ahmedabad | 2000.00 |
|  2 | Ganesh | 9830542648 | 758012203456 |  40 | Hyderabad | 2200.00 |
+----+--------+------------+--------------+-----+-----------+---------+