Table of Contents
UNIQUE Constraint in Oracle
An UNIQUE constraint specified that a column or a group of columns on which its defined should be unique.
There will be no duplicate value allows in unique key columns.
Types of UNIQUE Constraints:
- SINGLE COLUMN UNIQUE CONSTRAINT
- COMPOSITE UNIQUE CONSTRAINT
SINGLE COLUMN UNIQUE CONSTRAINT:
In this type of constraint the unique key is defined on a single column.
We can define this type of constraint both at column level or table level.
COLUMN LEVEL DEFINITION:
CREATE TABLE EMP_UNIQUE_TAB (EMPLOYEE_ID NUMBER(6) CONSTRAINT EMP_UNIQUE UNIQUE, FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), JOB_ID VARCHAR2(10), SALARY NUMBER(8, 2) );
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
TABLE LEVEL DEFINITION:
DROP TABLE EMP_UNIQUE_TAB;
CREATE TABLE EMP_UNIQUE_TAB (EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), JOB_ID VARCHAR2(10), SALARY NUMBER(8, 2), CONSTRAINT EMP_UNIQUE UNIQUE(EMPLOYEE_ID) );
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
COMPOSITE UNIQUE CONSTRAINT:
In this type of constraint the unique key is defined on a multiple columns.
We can define this type of constraint only at table level.
DROP TABLE EMP_UNIQUE_TAB;
CREATE TABLE EMP_UNIQUE_TAB (EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), JOB_ID VARCHAR2(10), SALARY NUMBER(8, 2), CONSTRAINT EMP_UNIQUE UNIQUE(EMPLOYEE_ID, FIRST_NAME) );
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
Types of UNIQUE constraint definition:
- In-line definition
- Out-of-line definition
In-line definition:
It states that UNIQUE constraint is defined during table creation, either at column level or at table level.
SINGLE COLUMN UNIQUE KEY DEFINITION:
CREATE TABLE EMP_UNIQUE_TAB (EMPLOYEE_ID NUMBER(6) CONSTRAINT EMP_UNIQUE UNIQUE, FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), JOB_ID VARCHAR2(10), SALARY NUMBER(8, 2) );
COMPOSITE UNIQUE KEY DEFINITION:
CREATE TABLE EMP_UNIQUE_TAB (EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), JOB_ID VARCHAR2(10), SALARY NUMBER(8, 2), CONSTRAINT EMP_UNIQUE UNIQUE(EMPLOYEE_ID, FIRST_NAME) );
Out-of-line definition:
It states that UNIQUE constraint is defined with ALTER TABLE statement.
SINGLE COLUMN UNIQUE KEY DEFINITION:
DROP TABLE EMP_UNIQUE_TAB;
CREATE TABLE EMP_UNIQUE_TAB (EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(30), LAST_NAME VARCHAR2(30), JOB_ID VARCHAR2(10), SALARY NUMBER(8, 2) );
USING MODIFY STATEMENT:
ALTER TABLE EMP_UNIQUE_TAB MODIFY(EMPLOYEE_ID NUMBER(6) CONSTRAINT EMP_UNIQUE UNIQUE);
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
USING ADD CONSTRAINT STATEMENT:
ALTER TABLE EMP_UNIQUE_TAB DROP CONSTRAINT EMP_UNIQUE;
ALTER TABLE EMP_UNIQUE_TAB ADD CONSTRAINT EMP_UNIQUE UNIQUE(EMPLOYEE_ID);
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
COMPOSITE UNIQUE KEY DEFINITION:
ALTER TABLE EMP_UNIQUE_TAB ADD CONSTRAINT EMP_UNIQUE UNIQUE(EMPLOYEE_ID, FIRST_NAME);
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
Disabling UNIQUE Constraint:
ALTER TABLE EMP_UNIQUE_TAB DISABLE CONSTRAINT EMP_UNIQUE;
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
Enabling UNIQUE Constraint:
ALTER TABLE EMP_UNIQUE_TAB ENABLE CONSTRAINT EMP_UNIQUE;
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
Drop UNIQUE Constraint:
ALTER TABLE EMP_UNIQUE_TAB DROP CONSTRAINT EMP_UNIQUE;
Querying the data dictionary:
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS, DEFERRABLE, DEFERRED, VALIDATED FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
Impact of UNIQUE Constraint on DML operations:
SINGLE COLUMN UNIQUE KEY:
INSERT:
ALTER TABLE EMP_UNIQUE_TAB ADD CONSTRAINT EMP_UNIQUE UNIQUE(EMPLOYEE_ID);
Inserting one record:
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(100, 'John', 'Smith', 'IT_PROG', 1000 );
Inserting same record again:
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(100, 'John', 'Smith', 'IT_PROG', 1000 );
UNIQUE constraint allows NULL values as unique data. So if we update the EMPLOYEE_ID as NULL in the above insert then oracle will allow to insert.
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(NULL, 'John', 'Smith', 'IT_PROG', 1000 );
SELECT * FROM EMP_UNIQUE_TAB;
We can insert multiple NULL values as EMPLOYEE_ID.
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(NULL, 'John', 'Smith', 'IT_PROG', 1000 );
SELECT * FROM EMP_UNIQUE_TAB;
UPDATE:
Updating one NULL value in above rows with 100.
UPDATE EMP_UNIQUE_TAB SET EMPLOYEE_ID = 100 WHERE EMPLOYEE_ID IS NULL AND ROWNUM = 1;
Updating with some other values:
UPDATE EMP_UNIQUE_TAB SET EMPLOYEE_ID = 101 WHERE EMPLOYEE_ID IS NULL AND ROWNUM = 1;
SELECT * FROM EMP_UNIQUE_TAB;
COMPOSITE UNIQUE KEY:
INSERT:
DELETE EMP_UNIQUE_TAB;
ALTER TABLE EMP_UNIQUE_TAB DROP CONSTRAINT EMP_UNIQUE;
ALTER TABLE EMP_UNIQUE_TAB ADD CONSTRAINT EMP_UNIQUE UNIQUE(EMPLOYEE_ID, FIRST_NAME);
Inserting one record:
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(100, 'John', 'Smith', 'IT_PROG', 1000 );
Inserting same record again:
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(100, 'John', 'Smith', 'IT_PROG', 1000 );
Now if we insert either FIRST_NAME or EMPLOYEE_ID as null or both as null then oracle will allow.
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(100, NULL, 'Smith', 'IT_PROG', 1000 );
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(NULL, 'John', 'Smith', 'IT_PROG', 1000 );
INSERT INTO EMP_UNIQUE_TAB(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY ) VALUES(NULL, NULL, 'Smith', 'IT_PROG', 1000 );
SELECT * FROM EMP_UNIQUE_TAB;
UPDATE:
Updating NULL value of FIRST_NAME column in above rows with Jhon where EMPLOYEE_ID is 100.
UPDATE EMP_UNIQUE_TAB SET FIRST_NAME = 'John' WHERE EMPLOYEE_ID = 100;
So we can see that UNIQUE constraint is working perfectly fine for NOT NULL values.
RELATED TOPICS: