SQL UNIQUE Constraint

SQL UNIQUE constraint prevents from entering identical values in same column for two records. SQL unique constraint on a table column makes sure that every row of that table contains a different value from other row in that column. SQL Unique constraint in very much similar to Primary Key constraint except that it allows NULL values to be entered into the column. As per the definition of Null Values, no two null values are identical; so SQL unique constraint will allow you to enter any number of NULL value rows. To avoid NULL values to be entered, you have to explicitly define NOT NULL constraint.

SQL Unique constraint is mostly used to restrict duplicate values from columns which are not protected by primary key.

ADDING A UNIQUE CONSTRAINT
SQL Unique can be added using

1. Create Statement
2. Alter Statement

 

Adding Unique Constraint using CREATE:

Example:
The following query will define SQL Unique constraint on column dept_name of department table using CREATE statement:

CREATE TABLE department (dept_no NUMBER(2), dept_name VARCHAR2(15) UNIQUE, mgr_id NUMBER,  mgr_start_date DATE);

Example 2:
The following query is same to one above except that it defines SQL unique constraint with name uniq_dept_name

CREATE TABLE department (dept_no NUMBER(2), dept_name VARCHAR2(15) CONSTRAINT uniq_dept_name UNIQUE, mgr_id NUMBER,  mgr_start_date DATE);

 

Adding Unique Constraint using ALTER:

Example:
The following query will define SQL unique constraint on dept_name column of department given that there is no SQL unique constraint existed earlier:

ALTER TABLE department ADD UNIQUE (dept_name);

Example 2:
The following query is same to one above except that it defines SQL unique constraint with name uniq_dept_name

ALTER TABLE department ADD CONSTRAINT uniq_dept_name UNIQUE (dept_name);

 

DROPPING A UNIQUE CONSTRAINT

Example:
The following statement will drop SQL unique constraint from dept_name of department table:

ALTER TABLE department DROP UNIQUE (dept_name);

Example 2:
The following statement will drop SQL unique constraint identified by its name:

ALTER TABLE department DROP CONSTRAINT uniq_dept_name;

Each table may have more than one SQL unique key. SQL unique key cannot be renamed.