Oracle Create Unique Index on a Column

Oracle Create Unique Index on a Column 

                          Unique index makes sure column has unique values.  it includes one NULL value too.

for ex:-  Create an Unique index of person's Email ID

Syntax:

              create unique index <index name> on tablename(col1,[col2,col3...]);

index_name is mandatory

tablename is mandatory

atleast one column should be present.

create a table called Person

SQL> create table person(id number(2),name varchar2(20),dob date, email varchar2(250));

Create unique Index on email ID

SQL>create unique index idx_persons_emailid_unique on person(email);

Insert data into Person table

INSERT INTO Person (Id, Name, dob, email) 

VALUES (1, 'Peter', to_date('1997-12-12','yyyy-mm-dd'), '1@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (6, 'life style', to_date('1997-09-11','yyyy-mm-dd'), NULL)
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (2, 'shyam purru', to_date('2000-01-12','yyyy-mm-dd'), '2@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (3, 'Jho', to_date('2002-11-01','yyyy-mm-dd'), '3@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) 

VALUES (4, 'Jhony', to_date('1960-08-30','yyyy-mm-dd'), '4@gmail.com')
 

INSERT INTO Person (Id, Name, dob, email) VALUES (5, 'pearso', to_date('2013-04-21 00:19:32','yyyy-mm-dd hh24:mi:ss'), '5@gmail.com')

Inserting duplicate records throws an exception/error

INSERT INTO Person (Id, Name, dob, email) VALUES (5, 'pearso', to_date('2013-04-21 00:19:32','yyyy-mm-dd hh24:mi:ss'), '5@gmail.com')

throws an error  because e-mail id 5@gmail.com already exists in the table.

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_PERSONS_EMAILID_UNIQUE) violated

Ex:  Creating an Unique Index on Existing Table with Data.

   Make sure all column values are unique

Tags: Oracle Create Unique Index on a Column , Create unique index syntax, oracle create unique index syntax, oracle plsql unique index, oracle unique index on single column,oracle create a unique index on existing table.