Updated February 28, 2023
Introduction to Oracle UNIQUE Constraint
Oracle Unique Constraint comes under Oracle integrity constraint which ensures only unique values that are stored in a column or group of columns (a unique constraint in oracle can only have a group of maximum 32 columns), here unique signifies that the data stored in every rows of a column is unique among the other rows of the column and the unique constraint allows null values in a column unlike the primary key provided the null value itself is unique to the column.
Syntax:
We can have unique constraint in our table by using both CREATE and ALTER command.
1. Syntax with CREATE statement
CREATE TABLE table (
...
column_name datatype UNIQUE
...
);
Parameters:
- Table: It refers to the name of the table.
- Column_name: It refers to the name of the column where we want to use the unique constraint.
2. Syntax with ALTER statement
ALTER TABLE table name
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
Parameters:
- Table: It refers to the name of the table.
- Column1: It refers to the name of the column where we want to use the unique constraint.
- unique_constraint_name: It refers to the name of the unique constraint.
How UNIQUE Constraint Works?
- Unique constraint in oracle is used to ensure that there are only unique values that are present in the rows of the column/columns of the tables.
- It is a table level constraint as well as column level constraint as you can add one or more columns while declaring the constraint.
- In Oracle we can declare the column as UNIQUE in two ways.
- The first one is to declare the column as unique when we are creating the table and along with it we provide the unique constraint for the columns we want them to be unique using SELECT statement.
- The second way is to use ALTER statement to add a unique constraint to a particular column.
- So, when unique constraint is applied on a column or columns the oracle database checks whether the new value entering the column with unique constraint is already present in the column.
- In case the value is already present, we get unique constraint violation message and in case the value is unique it is allowed to be inserted into that particular column.
- One more point to notice is that the Oracle database allows null values to be inserted into the column with unique constraint only if it is unique.
- For example if there are two columns column 1 and column 2 with unique constraint in a table, the combination of the values held by column 1 and column 2 will be unique as long as their values are not NULL.
- In case one of the columns holds a NULL value, there can be another identical row in the table.
Examples of Oracle UNIQUE Constraint
Given below are the examples:
Example #1
Single column UNIQUE constraint using CREATE statement.
In this case we are going to add unique constraint to a column in a table while creating the table.
In the example a table school will be created with school_id column being the column with UNIQUE constraint using the create statement.
Code:
CREATE TABLE school
( school_id numeric(10) NOT NULL,
school_name varchar2(50) NOT NULL,
city_area varchar2(50),
CONSTRAINT unique_school UNIQUE (school_id)
);
As we can see a unique constraint named unique_school has been added to the column school_id. One important point to note is that though unique constraint allows NULL value but since we have already add NOT NULL constraint with school_id column, This column will not allow null values.
Output:
Let us execute in SQL developer.
As per the screen shot the table school has been successfully created.
Example #2
Multiple Column UNIQUE Constraint using CREATE Statement.
In this case we are going to add unique constraint to more than one column in a table while creating the table.
In the example for this case a table school will be created with school_id and school_name columns being the columns with UNIQUE constraint in the table using the create statement.
Code:
CREATE TABLE school
( school_id numeric(10) NOT NULL,
school_name varchar2(50) NOT NULL,
city_area varchar2(50),
CONSTRAINT unique_school UNIQUE (school_id, school_name)
);
In the query we can see two columns named school_id and school_name having the unique constraint. One point to note is that since both columns already have NOT NULL constraint, hence they cannot have null values.
Output:
Let us execute in SQL developer.
As per the screen shot we can see the table school has been created.
Example #3
UNIQUE Constraint with ALTER Statement.
As stated earlier we can use ALTER statement to add unique constraint to a column or columns after the creation of the table.
In the example we are using ALTER statement to add unique constraint to the school_id column of the school table.
Code:
ALTER TABLE school
ADD CONSTRAINT unique_school UNIQUE (school_id);
Output:
Let us execute in SQL developer.
In the screen shot we can see that the table school has been altered.
Example #4
DROP UNIQUE Constraint.
We can also drop a existing unique constraint from a column of a table. THE DROP statement is used to drop the unique constraint from columns.
In this example we will drop the unique constraint from the column school_id present in table school.
Code:
ALTER TABLE school
DROP CONSTRAINT unique_school;
Output:
Let us run in SQL developer.
As per the screen shot the DROP statement has been executed successfully.
Conclusion
In this article we have discussed about the definition of UNIQUE Constraint in oracle along with the various syntaxes of using the unique constraint. We also discussed about the working of unique constraint and also went through various examples to have a better understanding.
Recommended Articles
This is a guide to Oracle UNIQUE Constraint. Here we discuss the introduction, how UNIQUE constraint works? and examples. You may also have a look at the following articles to learn more –
