Oracle Unique constraint vs Unique Index

Oracle Unique constraint vs Unique Index- Learn about unique constraint and unique index  creation and their difference with example programs.

For brief discussion about unique index and unique constraints , please click on hyperlinks.

A constraint has different meaning to an index. It allows you to have foreign keys on the column, whereas a unique index doesn’t.

Let’s discuss the difference between unique constraint and unique index with example.

Let’s create a table without key constraint on the column.

Create table without constraints

CREATE TABLE DEPARTMENT_TBL(DEPT_ID INT ,DEP_NAME VARCHAR2(100),DEPT_HEAD VARCHAR2(100));

Output

table DEPARTMENT_TBL created.

Apply unique index on the column

CREATE UNIQUE INDEX IDX_UNQ_DEPT_ID ON DEPARTMENT_TBL(DEPT_ID);

Output

unique index IDX_UNQ_DEPT_ID created.

Insert duplicate data into a table

Try to insert duplicate data into above created table.

INSERT INTO DEPARTMENT_TBL VALUES(1,'CSE','MADHU SUDHAN RAO');

Output

1 rows inserted.

INSERT INTO DEPARTMENT_TBL VALUES(1,'CSE','MADHU SUDHAN RAO');

Output

SQL Error: ORA-00001: unique constraint (DEVML510.IDX_UNQ_DEPT_ID) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.

Now you can observe that unique index is acting as unique constraint , which is restricting the duplicating entry into the table.

Let’s create another table that reference foreign key for the above created table.

Create table with foreign key constraint

CREATE TABLE STUDENT_TBL1(STD_ID INT PRIMARY KEY,STD_NAME VARCHAR2(100),MARKS NUMBER, DEPT_ID INT, CONSTRAINT FK_DEPID foreign KEY(DEPT_ID) REFERENCES DEPARTMENT_TBL(DEPT_ID));

Output

SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
gives a column-list for which there is no matching unique or primary
key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNS
catalog view

From above output you can observe that, the referenced table column doesn’t contain a unique or primary key. because of that you can create foreign key for the referencing table.

To apply foreign key on column of referenced table , column must contains a primary or unique key constraint.
unique index is not sufficient for that column.

Apply unique constraint on table

ALTER TABLE DEPARTMENT_TBL ADD CONSTRAINT UK_DEPT_ID unique( DEPT_ID);

Output

table DEPARTMENT_TBL altered.

Create table with foreign key constraint

CREATE TABLE STUDENT_TBL1(STD_ID INT PRIMARY KEY,STD_NAME VARCHAR2(100),MARKS NUMBER, DEPT_ID INT, CONSTRAINT FK1_DEPID foreign KEY(DEPT_ID) REFERENCES DEPARTMENT_TBL(DEPT_ID));

Output

table STUDENT_TBL1 created.

Now you can observe that we can create foreign key on column, because the reference table column contains unique key.

Important Points: