EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle UNIQUE Constraint
 

Oracle UNIQUE Constraint

Priya Pedamkar
Article byPriya Pedamkar

Updated February 28, 2023

Oracle UNIQUE Constraint

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

Oracle UNIQUE Constraint 1

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.

multiple column

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.

with alter statement

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.

Oracle UNIQUE Constraint 4

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 –

  1. Function in Oracle
  2. Natural Join in Oracle
  3. Oracle Full Outer Join
  4. GROUP BY in Oracle
  5. Guide to Oracle Window Functions
  6. Oracle While Loop | How to Works?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW