| ☰ See All Chapters |
Oracle UNIQUE Constraint
To avoid duplicate values to be added to a table, UNIQUE constraint is used on the column. According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values.
Below is the different syntax or different ways of adding UNIQUE constraint:
Syntax to add UNIQUE constraint in Oracle
Approach 1: Define unique constraint individually to each column
-------------Syntax ---------------
CREATE TABLE <table-name> ( <column-name1> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint> );
-------------Example---------------
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT NOT NULL UNIQUE, PHONE NUMERIC (10) NULL UNIQUE, EMAIL VARCHAR(30) UNIQUE );
|
Approach 2: Define unique constraint by UNIQUE statement
-------------Syntax ---------------
CREATE TABLE <table-name> ( <column-name1> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, UNIQUE(List of Column Names) );
-------------Example---------------
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT NOT NULL, PHONE NUMERIC (10) NULL, EMAIL VARCHAR(30) UNIQUE, UNIQUE (CUSTOMER_ID,PHONE) );
|
Approach 3: Define table and then update the constraint by ALTER statement
-------------Syntax---------------
CREATE TABLE <table-name> ( <column-name1> <data-type(size)> < NOT NULL/ NULL>, <column-name1> <data-type(size)> < NOT NULL/ NULL>, <column-name1> <data-type(size)> < NOT NULL/ NULL> );
ALTER TABLE <table-name>ADD ( CONSTRAINT <Name for Constraint> UNIQUE (List of Column Names) ); -------------Example---------------
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT NOT NULL, PHONE NUMERIC (10) NULL, EMAIL VARCHAR(30) );
ALTER TABLE CUSTOMER ADD ( CONSTRAINT CUSTOMER_UQ UNIQUE (CUSTOMER_ID,PHONE,EMAIL) );
|
Approach 4: Define table specific name to unique constraint by CONSTRAINT statement
-------------Syntax--------------- CREATE TABLE <table-name> ( <column-name1> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, CONSTRAINT <Name for Constraint> UNIQUE (List of Column Names) ); -------------Example---------------
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT NOT NULL, PHONE NUMERIC (10) NULL, EMAIL VARCHAR(30) UNIQUE, CONSTRAINT CUSTOMER_UQ UNIQUE (CUSTOMER_ID,PHONE,EMAIL) ); |
Drop UNIQUE constraint in Oracle
For dropping the unique constraint the column needs to be dropped and recreated again, this leads to loss of data.
Example to drop UNIQUE constraint in Oracle
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT NOT NULL UNIQUE, PHONE NUMERIC (10) NULL UNIQUE, EMAIL VARCHAR(30) UNIQUE );
ALTER TABLE CUSTOMER DROP (CUSTOMER_ID);
ALTER TABLE CUSTOMER ADD (CUSTOMER_ID INT NOT NULL); |
All Chapters
