UNIQUE Constraint in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

UNIQUE Constraint in Oracle with Examples

In this article, I am going to discuss UNIQUE Constraint in Oracle with Examples. Please read our previous article where we discussed the basics of Constraints in Oracle. At the end of this article, you will understand everything about the UNIQUE Constraint in Oracle with Examples

UNIQUE Constraint in Oracle

When we want a column or columns not to accept any duplicate values, then we need to use UNIQUE Constraint for that column or columns in Oracle. That means the UNIQUE Constraint in Oracle is useful to restrict storing of duplicate data row values in a given column or combination of columns. But it accepts NULL values in that column.

Multiple columns in a single table can have the UNIQUE Constraint. We can apply the UNIQUE constraint on any data type column such as INT, VARCHAR, etc.

So, the UNIQUE Constraint in Oracle is used to restrict duplicate values but accept nulls into a column. Again, the unique constraint can be applied either at the column level or at the table level.

Note: When we apply a unique constraint on a group of columns then we called it a “composite unique” constraint. In this mechanism, individual columns are accepting duplicate values but the duplicate combination of columns data is not allowed.

Example to understand UNIQUE Constraint in Oracle:

Let us understand the Unique Constraint in Oracle with some real-time examples. In order to understand MySQL Unique Constraint, create the Employee table by executing the following SQL Script. As you can see, here, we applied the UNIQUE constraint to Id, and Email columns. It means these two columns will not accept duplicate values.

DROP Table Employee;
CREATE TABLE Employee (
  Id INT UNIQUE,
  Name VARCHAR(10),
  Email VARCHAR(20) UNIQUE,
  Department VARCHAR(10)
);

Now try to execute the following Insert statements.

INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'Anurag', 'Anurag@g.com', 'IT');

When we execute the above INSERT statement, it is executed as expected and you will see that 1 row is inserted as shown in the below image.

Now, try to execute the following Insert Statement.

INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'Sambit', 'Sambit@g.com', 'IT');

When we try to execute the above INSERT statement, you will get the error ORA-00001: unique constraint (SYSTEM.SYS_C007621) violated as shown in the below image. This is because we are trying to insert duplicate Id values into the Id column.

Now, try to execute the below SQL INSERT Statement.

INSERT INTO Employee (Id, Name, Email, Department) VALUES (2, 'Sambit', 'Anurag@g.com', 'IT');

Now, when you try to execute the above INSERT SQL statement, you will get the error ORA-00001: unique constraint (SYSTEM.SYS_C007622) violated as shown in the below image. This is because now we are trying to insert duplicate Email values into the Email column.

When we try to insert a duplicate value (other than null) into a column on which the UNIQUE constraint is applied, then the SQL statement gets terminated by displaying an error message. Now, execute the below INSERT Statement.

INSERT INTO Employee (Id, Name, Email, Department) VALUES (NULL, 'Sambit', NULL, 'IT');

The above SQL statement will be executed successfully as shown in the below image. This is because the UNIQUE Constraint allows null value.

How to add UNIQUE Constraints to existing Columns in Oracle?

It is also possible in Oracle to add UNIQUE Constraints to existing columns of a table. Let us understand this with an example. First, delete all the data from the Employee table by executing the below TRUNCATE statement.

TRUNCATE Table Employee;

In our Employee table, suppose we want to add UNIQUE Constraint on the Name column. Then we can do the same by executing the below SQL Statement.

ALTER TABLE Employee ADD UNIQUE (Name);

Once you execute the above query, it will create a Unique Constraint on the Name column.

Imposing UNIQUE Constraint at Column Level:

Let us understand how to Impose a Unique Constraint at column level with an example. As you can see in the below Create Table statement, we have specified UNIQUE Constraints directly at the column level for the Id and Name Columns.

CREATE TABLE TEST1(Id INT UNIQUE, Name VARCHAR2(10) UNIQUE);

Testing:
INSERT INTO TEST1 VALUES (1, ‘A’); —Allowed
INSERT INTO TEST1 VALUES (1, ‘A’); —Not Allowed
INSERT INTO TEST1 VALUES (NULL, NULL); —-Allowed

Imposing UNIQUE Constraint at Table Level:

Let us understand how to impose UNIQUE constraints at table level with an example. As you can see in the below Create Table statement, first, we created all the columns and then we created UNIQUE constraints and while creating UNIQUE constraint we specified the column name to which the constraint belongs.

CREATE TABLE Test2
( 
 Id      INT, 
 Name    VARCHAR(50), 
 Email   VARCHAR(10), 
 UNIQUE(Name),
 UNIQUE(Email)
);

Testing:

INSERT INTO TEST2 VALUES(1,’A’, ‘A@G.COM’); —Allowed
INSERT INTO TEST2 VALUES(2,’A’, ‘B@G.COM’); —Not Allowed
INSERT INTO TEST2 VALUES(3,’C’, ‘A@G.COM’); —NOT Allowed

Note: There is no difference in behavior whether we imposed the UNIQUE Constraint at the table level or at the column level but if we imposed the constraint at the table level, then we have the chance to impose the composite UNIQUE constraints. 

Composite UNIQUE Constraint in Oracle:

It is also possible in Oracle to apply a single UNIQUE Constraint on multiple columns. In this case, duplicate values are allowed on a single column, but the combination of duplicate values is not allowed. If this is not clear at the moment, then don’t worry we will try to understand the same with an example. Please execute the below SQL Script to create the Customer table.

CREATE TABLE Customer 
( 
    Id    INT NOT NULL, 
    Name  VARCHAR(30), 
    Email VARCHAR(50),
    UNIQUE (Name, Email)
);

Here, we have created one UNIQUE Constraint which is applied to the Name and Email Column of the Customer table. Now, execute the below INSERT SQL Statements.

INSERT INTO Customer (Id, Name, Email) VALUES (1, 'Sambit', 'S@G.COM');
INSERT INTO Customer (Id, Name, Email) VALUES (2, 'Sambit', 'S1@G.COM');
INSERT INTO Customer (Id, Name, Email) VALUES (3, 'Sambit1', 'S1@G.COM');

Once you execute the above SQL Queries, three records are inserted into the Customer table as shown in the below image.

Here, if you notice. In the second and third SQL Statements, we have some duplicate values. In the second INSERT statement, the Name is duplicate and in the third INSERT statement, the Email is duplicate. But all these records are inserted, this is because the combination of Name and Email is not duplicated. Now, try to execute the below SQL Statement.

INSERT INTO Customer (Id, Name, Email) VALUES (4, 'Sambit', 'S@G.COM');

Now, when you try to execute the above INSERT Query, you will get the error ORA-00001: unique constraint (SYSTEM.SYS_C007627) violated as shown in the below image. This is because the combination of the above Name and Email is already existing in the database.

In the next article, I am going to discuss NOT NULL Constraint in Oracle with Examples. Here, in this article, I try to explain UNIQUE Constraint in Oracle with Examples and I hope you enjoy this UNIQUE Constraint in Oracle with Examples article.