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: