How To Create, Drop, Disable and Enable Unique Constraints in Oracle PLSQL
Unique Constraint
Unique constraint is like an alternative primary key constraint. A unique constraint defines a single field or a combination of fields, which must be unique in value. Some of the field cannot be null values, as long as the combination of the values is unique.
A unique constraint cannot contain more than 32 columns in Oracle.
Difference between primary key and unique constraint
- Primary key does not accept null values.
- Unique constraint accepts the null values.
- We can create only one primary key in a table.
- We can create a number of unique keys in a table.
Syntax
The syntax for creating a unique constraint uses a create table statement.
- Create table table_name
- (
- column 1 datatype(),
- column 2 datatype(),
- . .
- . .
- column n datatype(),
- constraint constraint_name unique(column 1, column 2,……column n)
- );
Example
- Create table Person
- (
- Person_Id number(20) NOT NULL,
- person_mob number(10),
- person_Name varchar2(25),
- person_Address varchar2(100),
- person_City varchar2(30),
- CONSTRAINT uc_PersonID UNIQUE (Person_Id, person_mob )
- );
- COMMIT;
Inserting values
- insert into person
- (person_id, person_mob, person_Name, person_Address, person_City)
- values
- (101,9812654215, 'Ram', 'h-block', 'Noida');
- insert into person
- (person_id, person_mob, person_Name, person_Address, person_City)
- values
- (102,9852536345, 'shiv', 'l-block', 'Delhi');
- insert into person
- (person_id, person_mob, person_Name, person_Address, person_City)
- values
- (103, 9854121232, 'Vijay', 'DLF', 'Gurgaon');
- insert into person
- (person_id, person_mob, person_Name, person_Address, person_City)
- values
- (104, 9854121232 , 'Vijay', 'DLF', 'Gurgaon');
- COMMIT;

Output

Drop Unique Constraint
Syntax
In the syntax, given below, you will be dropping a unique constraint in Oracle.
- Alter table table_name
- Drop constraint constraint_name;
- COMMIT;
Example
In the example, given below, you will drop a constraint, using Alter table command in Oracle.
- Alter table person
- DROP CONSTRAINT uc_person_id;
- COMMIT;

Output

Disable Unique Constraint
Syntax
In the syntax, given below, you will disable the unique constraint in Oracle.
- Alter table table_name
- DISABLE CONSTRAINT constraint_name;
Example
In the example, given below, you will use DISABLE constraints, using Alter table command in Oracle.
- Alter table person
- DISABLE CONSTRAINT uc_person_id;
- COMMIT;

Output

Enable Unique Constraint
Syntax
In the syntax, given below, you will enable unique constraint in Oracle.
- Alter table table_name
- ENABLE CONSTRAINT constraint_name;
Example
In the example, given below, you will ENABLE constraints, using Alter table command in Oracle.
- Alter table person
- ENABLE CONSTRAINT uc_person_id;
- COMMIT;

Output

Summary