«Back to Home

Oracle Jump Start
Topics

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.
“Oracle does not permit you to create both a primary key and unique constraint with the same columns.”
 
Syntax
 
The syntax for creating a unique constraint uses a create table statement.
  1. Create table table_name  
  2. (  
  3. column 1 datatype(),  
  4. column 2 datatype(),  
  5. . .  
  6. . .  
  7. column n datatype(),  
  8. constraint constraint_name unique(column 1, column 2,……column n)  
  9. );  
Example
 
In the example, given below, you will create table person, using a create table statement.
  1. Create table Person  
  2. (  
  3. Person_Id number(20) NOT NULL,  
  4. person_mob number(10),  
  5. person_Name varchar2(25),  
  6. person_Address varchar2(100),  
  7. person_City varchar2(30),  
  8. CONSTRAINT uc_PersonID UNIQUE (Person_Id, person_mob )  
  9. );  
  10. COMMIT;  
Inserting values
  1. insert into person  
  2. (person_id, person_mob, person_Name, person_Address, person_City)  
  3. values  
  4. (101,9812654215, 'Ram', 'h-block', 'Noida');  
  5. insert into person  
  6. (person_id, person_mob, person_Name, person_Address, person_City)  
  7. values  
  8. (102,9852536345, 'shiv', 'l-block', 'Delhi');  
  9. insert into person  
  10. (person_id, person_mob, person_Name, person_Address, person_City)  
  11. values  
  12. (103, 9854121232, 'Vijay', 'DLF', 'Gurgaon');  
  13. insert into person  
  14. (person_id, person_mob, person_Name, person_Address, person_City)  
  15. values  
  16. (104, 9854121232 , 'Vijay', 'DLF', 'Gurgaon');  
  17.   
  18. COMMIT;  
1.1

Output
2.2 

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

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

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

Output
 
8.8 

Summary
 
Thus, we learnt, unique constraint is like an alternative to primary key constraints. A unique constraint defines a single field or a combination of fields, which must be unique in value. We learnt, how to use unique constraints in Oracle with the examples.