In Oracle SQL / PLSQL a UNIQUE CONSTRAINT is single or group of column(s)/field(s) that can uniquely identify each record in a table.
Some of the column(s) / field(s) of unique constraint can have NULL values until the combination can uniquely identify each record of table.
Important points about UNIQUE CONSTRAINT in Oracle SQL / PLSQL:
- In Oracle SQL / PLSQL a UNIQUE CONSTRAINT can be created by using maximum of 32 columns / fields.
- A UNIQUE CONSTRAINT can be defined in CREATE TABLE or ALTER TABLE statement.
Syntax to create a UNIQUE CONSTRAINT in CREATE TABLE statement is:
CREATE TABLE table_name
(column_name1 datatype NULL/NOT NULL
,column_name2 datatype NULL/NOT NULL
,column_name3 datatype NULL/NOT NULL
.
.
,column_nameN datatype NULL/NOT NULL
,CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, . . . ,column_nameN );
Syntax to create a UNIQUE CONSTRAINT in ALTER TABLE statement is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, . . . ,column_nameN );
Let’s take an example for understanding:
Scenario 1:
Step 1: Suppose we want to create a table named ‘employee’ in the database as shown below, with ‘employee_id’ column having UNIQUE CONSTRAINT.
| Employee_Id | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
We can achieve the same as:
CREATE TABLE employee (employee_id NUMBER(10) NOT NULL ,employee_name VARCHAR2(500) NOT NULL ,salary NUMBER(20) NOT NULL ,department VARCHAR2(300) NOT NULL ,commission NUMBER(20) ,CONSTRAINT employee_pk UNIQUE (employee_id) );
Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 5 columns namely ‘employee_id’, ‘employee_name’, ‘department’, ‘salary’ and ‘commission’. Also we are having UNIQUE CONSTRAINT on ‘employee_id’ column of the ‘employee’ table.
Step 2: Inserting the data in the ‘employee’ table.
INSERT INTO employee VALUES (101,'Emp A',10000,'Sales',10); INSERT INTO employee VALUES (102,'Emp B',20000,'IT',20); INSERT INTO employee VALUES (103,'Emp C',28000,'IT',20); INSERT INTO employee VALUES (104,'Emp D',30000,'Support',NULL); INSERT INTO employee VALUES (105,'Emp E',32000,'Sales',10);
The above SQL INSERT statements will insert 5 rows in the ‘employee’ table.
Now, if we query the employee table as:
SELECT * FROM employee;
We will get the following result:
| Employee_Id | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
Here we have successfully created ‘employee’ table with column ‘employee_id’ having UNIQUE CONSTRAINT and also inserted data in ‘employee’ table.
If we try to insert one more record of employee_id = 105 as:
INSERT INTO employee VALUES (105,'Emp F',40000,'Sales',30);
We get an error that unique constraint is getting violated and the data cannot be entered, this is because ‘employee_id’ column has UNIQUE CONSTRAINT and for employee_id = 105 we already have a record in ‘employee’ table and hence one more record of the same key cannot be entered.
Scenario 2:
Creating a combination of columns as UNIQUE CONSTRAINT
Suppose we want to create a table named ‘employee’ in the database as shown below, with combination of ‘employee_id’ and ‘employee_name’ as the UNIQUE CONSTRAINT columns for the ‘employee’ table
| Employee_Id | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
We can achieve the same as:
CREATE TABLE employee (employee_id NUMBER(10) NOT NULL ,employee_name VARCHAR2(500) NOT NULL ,salary NUMBER(20) NOT NULL ,department VARCHAR2(300) NOT NULL ,commission NUMBER(20) ,CONSTRAINT employee_pk UNIQUE (employee_id,employee_name) );
Let’s insert some data in the newly created ‘employee’ table
INSERT INTO employee VALUES (101,'Emp A',10000,'Sales',10); INSERT INTO employee VALUES (102,'Emp B',20000,'IT',20); INSERT INTO employee VALUES (103,'Emp C',28000,'IT',20); INSERT INTO employee VALUES (104,'Emp D',30000,'Support',NULL); INSERT INTO employee VALUES (105,'Emp E',32000,'Sales',10);
The above SQL INSERT statements will insert 5 rows in the ‘employee’ table.
Now, if we query the employee table as:
SELECT * FROM employee;
We will get the following result:
| Employee_Id | Employee_Name | Salary | Department | Commission |
| 101 | Emp A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
Suppose we want to insert one more record with ‘employee_id = 105’ and ‘employee_name = Emp F’ then we can do this and oracle will not give any error because the combination of ‘employee_id’ and ‘employee_name’ is still unique.
Scenario 3:
Using ALTER TABLE statement to create UNIQUE CONSTRAINT on a table
As mentioned earlier that an ALTER TABLE statement can be used to create UNIQUE CONSTRAINT on a table.
The syntax to create UNIQUE CONSTRAINT using ALTER TABLE statement is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name1,column_name2, . . column_nameN);
Example:
ALTER TABLE employee ADD CONSTRAINT employee_pk UNIQUE (employee_id);
Here we have altered the ‘employee’ table and have successfully created UNIQUE CONSTRAINT on ‘employee_id’ column.
We can also create UNIQUE CONSTRAINT by a combination of columns using the ALTER TABLE statement
Example:
ALTER TABLE employee ADD CONSTRAINT employee_pk UNIQUE (employee_id, employee_name);
Here we have altered the ‘employee’ table and have successfully created UNIQUE CONSTRAINT on ‘employee_id’ and ‘employee_name’ column.