Oracle UNIQUE Constraint

In the Oracle database, the UNIQUE constraint is used to prevent duplicate data entry into a table. But it doesn’t prevent NULL values entry.

A UNIQUE constraint is an integrity constraint that ensures the data stored in a column, or a group of columns, is unique among the rows in a table.

We can apply a UNIQUE constraint on a table at two levels,
a) Column level:- UNIQUE constraint applied only on a particular column.
b) Table level:- UNIQUE constraint applied to the combination of multiple columns.

UNIQUE Constraint at Column level

When we enable UNIQUE constraint on column level then those column doesn’t accept duplicate data but they accepts null values.

Syntax to create UNIQUE constraint on column level:-
CREATE TABLE tablename (
col1 datatype(size) UNIQUE,
col2 datatype(size) UNIQUE,
….
);

Example:-

SQL> CREATE TABLE uniquetest (
       sno NUMBER(10) UNIQUE,
       name VARCHAR2(10)
     );
Table created.

SQL> INSERT INTO uniquetest VALUES(1, 'abc');
1 row created.

SQL> INSERT INTO uniquetest VALUES(1, 'xyz');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014078) violated

The sno column of uniquetest table having UNIQUE constraint so, we can’t insert duplicate data on this column. If we try to insert duplicate values then Oracle gives error:- ORA-00001: unique constraint violated. But we can insert null values on this column.

SQL> INSERT INTO uniquetest VALUES(2, 'xyz');
SQL> INSERT INTO uniquetest VALUES(NULL, 'xyz');
SQL> INSERT INTO uniquetest VALUES(NULL, 'pqr');
SQL> SELECT * FROM uniquetest;
       SNO NAME
---------- ----------
         1 abc
         2 xyz
           xyz
           pqr

In a table there can be multiple columns having UNIQUE constraint. For example:-

SQL> CREATE TABLE users(
       id NUMBER(10) UNIQUE, 
       name VARCHAR2(10) UNIQUE,
       pass VARCHAR2(10)
     );
Table created.

The “users” table having id, name, and password columns where id and name columns having UNIQUE constraint. Therefore, we can’t store duplicate data on id column and also can’t store duplicate value on the name column.

SQL> INSERT INTO users VALUES(1, 'sophia', 'random11');
1 row created.

SQL> INSERT INTO users VALUES(1, 'william', '123william');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014079) violated

SQL> INSERT INTO users VALUES(2, 'sophia', 'sfdf51');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014080) violated

SQL> INSERT INTO users VALUES(2, 'william', 'sfdf51');
1 row created.

SQL> SELECT * FROM users;
        ID NAME       PASS
---------- ---------- ----------
         1 sophia     random11
         2 william    sfdf51

UNIQUE Constraint at Table Level

When the UNIQUE constraint is enabled on table level then duplicate data entry is prevented on the combination of these columns, not on a particular column. In this case, it will give an error, only when all input of applied UNIQUE constraints columns values are the same.

Syntax to create UNIQUE constraint at table level:-
CREATE TABLE tablename(
col1 datatype(size),
col2 datatype(size),
….,
UNIQUE(col1, col2, …)
);

Examples:-

SQL> CREATE TABLE u1(
       sno NUMBER(10),
       name VARCHAR2(10), 
       address VARCHAR2(10),
       UNIQUE(sno, name)
     );
Table created.

The UNIQUE constraint is applied to the combination of sno and name column of the u1 table. Therefore, when entered values are exactly matched with both columns then only Oracle will give an error.

SQL> INSERT INTO u1 VALUES(1, 'sophia', 'xyz');
SQL> INSERT INTO u1 VALUES(1, 'william', 'abc');
SQL> INSERT INTO u1 VALUES(1, 'alexa', 'abc');
SQL> INSERT INTO u1 VALUES(2, 'sophia', 'abc');
SQL> INSERT INTO u1 VALUES(2, 'william', 'abc');
SQL> INSERT INTO u1 VALUES(2, 'alexa', 'abc');
SQL> SELECT * FROM u1;
       SNO NAME       ADDRESS
---------- ---------- ----------
         1 sophia     xyz
         1 william    abc
         1 alexa      abc
         2 sophia     abc
         2 william    abc
         2 alexa      abc
6 rows selected.

SQL> INSERT INTO u1 VALUES(1, 'sophia', 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014081) violated

SQL> INSERT INTO u1 VALUES(2, 'sophia', 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014081) violated

When we inserted values(1, ‘william’, ‘abc’) then only sno=1 is matched with existing values in sno column but name value is unique compared to the existing value in the name column, that’s why it is allowed.

But at last, when we try to insert (1, ‘sophia’, ‘pqr’), then both sno=1 and name=’sophia’ is matched with existing data that’s why Oracle gives error:- ORA-00001: unique constraint violated

While using UNIQUE constraint we are allowed to store null values, it won’t treated as duplicate values.

SQL> INSERT INTO u1 VALUES(null, null, null);
SQL> INSERT INTO u1 VALUES(null, null, 'abc');
SQL> INSERT INTO u1 VALUES(null, 'sophia', 'abc');

SQL> INSERT INTO u1 VALUES(null, 'sophia', 'abc');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014081) violated

Add UNIQUE Constraint In Oracle

When we copied a table from another table then except NOT NULL constraint all other constraints are never copied. So, we need to enable constraints on the existing column of a table.

We can add UNIQUE constraint on an existing column of a table, or we can add a new column in the table with UNIQUE constraint.

Enable UNIQUE constraint on an existing column

Condition:- column must not have duplicate values. Otherwise Oracle gives error:- ORA-02299: cannot validate – duplicate keys found

Syntax,
ALTER TABLE tablename
ADD UNIQUE(columnName);

At a time, using this syntax we can enable UNIQUE constraint only on one column.

Example:- The users table contains “pass” column and contains unique values so we can enable UNIQUE constraint on “pass” column.

SQL> SELECT * FROM users;
        ID NAME       PASS
---------- ---------- ----------
         1 sophia     random11
         2 william    sfdf51

SQL> ALTER TABLE users 
     ADD UNIQUE(pass);
Table altered.

We can also enable UNIQUE constraints on multiple existing columns of a table at table level. For this, we have to follow this syntax,
ALTER TABLE tablename
ADD UNIQUE(col1, col2, …, col_N);

Again, the combination of these columns must not have duplicate values else we can’t enable UNIQUE constraint.

Add UNIQUE constraint on new column

Syntax to create a new column with a UNIQUE constraint,
ALTER TABLE tablename
ADD columnName datatype(size) UNIQUE;

Example:-

SQL> ALTER TABLE u1
     ADD id NUMBER(10) UNIQUE;
Table altered.

Using above syntax we can add only column at a time, so for multiple columns we have to execute same query with different columns for multiple times.

Alternate way:- Add column to the table and then enable UNIQUE constraint on existing column.

Dropping the Constraint

We can remove constraints by using its name. The syntax to drop or remove the constraint from the table is,
ALTER TABLE tableName
DROP CONSTRAINTS constraint_name;

Using Pre-defined Constraint Name

In the Oracle database, whenever we are creating a constraint then the Oracle server internally automatically generates a unique identification number for identifying a constraint uniquely in the format of SYS_Cn, this is also called a pre-defined constraint name. Example:-

SQL> CREATE TABLE u2 (
       sno NUMBER(10) UNIQUE,
       name VARCHAR2(10) UNIQUE
     );
Table created.

SQL> INSERT INTO u2 VALUES(1, 'abc');
1 row created.

SQL> INSERT INTO u2 VALUES(1, 'pqr');
ORA-00001: unique constraint (SCOTT.SYS_C0014086) violated

SQL> INSERT INTO u2 VALUES(2, 'abc');
ORA-00001: unique constraint (SCOTT.SYS_C0014087) violated

Here SYS_C0014086 and SYS_C0014087 are the Oracle generated unique identification number of constraint applied on sno and name columns of u2 table. We can get this information from user_cons_columns.

SQL> DESC user_cons_columns;
 Name              Null?    Type
 ----------------- -------- --------------
 OWNER             NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME   NOT NULL VARCHAR2(30)
 TABLE_NAME        NOT NULL VARCHAR2(30)
 COLUMN_NAME                VARCHAR2(4000)
 POSITION                   NUMBER

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'U2';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014086
NAME            SYS_C0014087

Note that in the above query table name U2 is given in the capital letter. Oracle table name, column names are not case sensitive but records of a table are case sensitive, and here U2 is a record of user_cons_columns.

To drop the constraint we can use these constraint names.

SQL> ALTER TABLE u2 
     DROP CONSTRAINTS SYS_C0014086;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'U2';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
NAME            SYS_C0014087

When Constraint is enabled at table level then the pre-defined constraint name will be same of those columns.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'U1';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014081
NAME            SYS_C0014081
ID              SYS_C0014085

SQL> ALTER TABLE u1 
     DROP CONSTRAINTS SYS_C0014081;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'U1';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
ID              SYS_C0014085

Assigning User-defined Name to Constraint

While dropping the constraints, the pre-defined constraint name creates confusion for others, and just by seeing the pre-defined constraint name, we can’t identify which constraint is enabled on this column.

In place of a pre-defined constraint name, we can also assign our own name by using the CONSTRAINT keyword by using the following syntax. This is called a user-defined constraint name. Syntax:- CONSTRAINT user-definedName constriantType

Example:-

SQL> CREATE TABLE u3 (
       sno NUMBER(10) CONSTRAINT unique_sno UNIQUE,
       name VARCHAR2(10)
     );
Table created.

SQL> ALTER TABLE u3 
     ADD CONSTRAINT unique_name UNIQUE(name);
Table altered.

SQL> ALTER TABLE u3 
     ADD address VARCHAR2(10) CONSTRAINT unique_address UNIQUE;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'U3';
COLUMN_NAME    CONSTRAINT_NAME
-------------- --------------
SNO            UNIQUE_SNO
NAME           UNIQUE_NAME
ADDRESS        UNIQUE_ADDRESS

Now we can identify these constraint easily and drop it by the previous syntax.

Note:- User-defined constraint name must be unique within a user else Oracle gives error:- ORA-02264: name already used by an existing constraint

SQL> create table u4(
       sno number(10) constraint unique_sno unique
     );
ERROR at line 1:
ORA-02264: name already used by an existing constraint

Enable and Disable the UNIQUE Constraint

We can enable and disable the unique constraints. For this we have to use constraint_name.

Syntax to disable a constraint,
ALTER TABLE tableName
DISABLE CONSTRAINT constraint_name;

Syntax to enable constraint,
ALTER TABLE tableName
ENABLE CONSTRAINT constraint_name;

Note:- We can only enable the constraint if the applied columns don’t contain the violated conditions.

Example:-

SQL> ALTER TABLE u3
     DISABLE CONSTRAINT UNIQUE_SNO;
Table altered.

SQL> INSERT INTO u3 VALUES(1, 'abc', 'xyz');
SQL> INSERT INTO u3 VALUES(1, 'pqr', 'uvw');
SQL> SELECT * FROM u3;
       SNO NAME       ADDRESS
---------- ---------- ----------
         1 abc        xyz
         1 pqr        uvw

Again, to enable the UNIQUE constraint column must not have duplicate values.

SQL> ALTER TABLE u3
     ENABLE CONSTRAINT UNIQUE_SNO;
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.UNIQUE_SNO) - duplicate keys found

We have to remove or update the duplicate values.

SQL> UPDATE u3 SET sno=2
     WHERE name = 'pqr';
1 row updated.

SQL> SELECT * FROM u3;
       SNO NAME       ADDRESS
---------- ---------- ----------
         1 abc        xyz
         2 pqr        uvw

SQL> ALTER TABLE u3
     ENABLE CONSTRAINT UNIQUE_SNO;
Table altered.

SQL> INSERT INTO u3 VALUES(1, 'aaa', 'bbb');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UNIQUE_SNO) violated

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint

Search