Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

UNIQUE Constraint in Oracle

Leave a Comment / SQL, CONSTRAINTS / By

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';
UNIQUE Constraint in Oracle: output
UNIQUE Constraint in Oracle : output
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';
UNIQUE Constraint in Oracle : output

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';
UNIQUE Constraint in Oracle : output
SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME 
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_UNIQUE_TAB';
UNIQUE Constraint in Oracle : output

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';
UNIQUE Constraint in Oracle : output

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';
UNIQUE Constraint in Oracle : output

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 in Oracle : error output

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;
UNIQUE Constraint in Oracle : error output

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:

Author

Leave a Comment

Your email address will not be published.