UNIQUE Constraint in Oracle

A unique constraint in Oracle ensures the storage of unique data in a column or group of columns.

This constraint helps in maintaining data integrity.

As long as the data set in the group of columns is unique, entry of null values is valid in this constraint.

UNIQUE CONSTRAINT

Syntax

  • The column-level syntax of Unique
CREATE TABLE table_name
(
column1 datatype UNIQUE,
column2 datatype,

);
  • Table level syntax of Unique
CREATE TABLE table_name
(
column1 datatype,
column2 datatype,
column3 datatype,

CONSTRAINT constraint_name UNIQUE (column1, column2));

);
  • Syntax of Unique in Alter statement
ALTER TABLE table_name ADD CONSTRAINT 
constraint_name UNIQUE (column1, column2);
Parameters Explanation

column1, column2, column3

  • Single or multiple columns of the table

constraint_name

  • User-defined constraint name of UNIQUE

table_name                                                  

  • Single or multiple tables

datatype

  • The data type of columns in the table

Example

  • At column level
CREATE TABLE employees
(empid NUMBER,
empname VARCHAR2(50),
national_identifier VARCHAR2(20) UNIQUE,

);

In this example, national_identifier stores unique data. In case of duplication, the error is thrown.

  • At table level
CREATE TABLE employees
(empid NUMBER,
empname VARCHAR2(50),
national_identifier VARCHAR2(20),
country VARCHAR2(20),

CONSTAINT emp_uc UNIQUE(national_identifier, country) );

Here, a unique constraint combining national_identifier and country is defined at the table level.

  • Alter statement
ALTER TABLE employees ADD CONSTRAINT emp_uc UNIQUE(national_identifier);

Here, a unique constraint emp_uc is defined using the alter table statement.

Please feel free to write us at connect@oraclemine.com or hioraclemine@gmail.com if you find mistakes or errors in any of the topics. Your suggestions to improve article helps us improve.

You can also Subscribe here to stay updated on the latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.