Oracle Unique Constraint

Summary: in this tutorial, you will learn how to use the Oracle unique constraint to ensure the data contained in a column, or a group of columns, is unique among the rows in the table.

Oracle unique constraint syntax

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.

Typically, you apply the unique constraints to columns when you create the table using the inline constraint syntax as follows:

CREATE TABLE table_name (
    ...
    column_name data_type UNIQUE
    ...
);
Code language: SQL (Structured Query Language) (sql)

This unique constraint specifies that the values in the column_name is unique across the whole table.

You can also use the out-of-line constraint syntax to define a unique constraint:

CREATE TABLE table_name (
    ...,
    UNIQUE(column_name)
);
Code language: SQL (Structured Query Language) (sql)

It’s possible to assign a unique constraint a name by using the CONSTRAINT clause followed by the constraint name:

CREATE TABLE table_name (
    ...
    column_name data_type CONSTRAINT unique_constraint_name UNIQUE
    ...
);
Code language: SQL (Structured Query Language) (sql)

or with out-of-line constraint syntax:

CREATE TABLE table_name (
    ...
    column_name data_type,
    ...,
    CONSTRAINT unique_constraint_name UNIQUE(column_name)
);
Code language: SQL (Structured Query Language) (sql)

To define a unique constraint for a group of columns, you use the out-of-line constraint syntax:

CREATE TABLE table_name (
    ...
    column_name1 data_type,
    column_name2 data_type,
    ...,
    CONSTRAINT unique_constraint_name UNIQUE(column_name1, column_name2)
);
Code language: SQL (Structured Query Language) (sql)

This specifies that the combination of values in the column_name1 and column_name2 is unique across the whole table, though any one of these columns need not be unique.

If you want to add a unique constraint to an existing table, you use the ALTER TABLE statement:

ALTER TABLE table_name
ADD CONSTRAINT unique_constraint_name UNIQUE(column_name1, column_nam2);
Code language: SQL (Structured Query Language) (sql)

Sometimes, you may want to disable a unique constraint temporarily:

ALTER TABLE table_name
DISABLE CONSTRAINT unique_constraint_name;
Code language: SQL (Structured Query Language) (sql)

And then enable it:

ALTER TABLE table_name
ENABLE CONSTRAINT unique_constraint_name;
Code language: SQL (Structured Query Language) (sql)

Or even drop a unique constraint:

ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;
Code language: SQL (Structured Query Language) (sql)

Oracle unique constraint examples

Let’s create a table named clients for the demonstration:

CREATE TABLE clients (
    client_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    company_name VARCHAR2(255) NOT NULL,
    email VARCHAR2(255) NOT NULL UNIQUE,
    phone VARCHAR(25)
);
Code language: SQL (Structured Query Language) (sql)

The email column has a unique constraint that ensures there will be no duplicate emails.

The following statement inserts a row into the clients table:

INSERT INTO clients(first_name,last_name, email, company_name, phone)
VALUES('Christene','Snider','[email protected]', 'ABC Inc', '408-875-6075');
Code language: SQL (Structured Query Language) (sql)

Now, we attempt to insert a new row whose email value already exists in the email column:

INSERT INTO clients(first_name,last_name, email, company_name, phone)
VALUES('Sherly','Snider','[email protected]', 'ABC Inc', '408-875-6076');
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error message indicating that the unique constraint has been violated:

SQL Error: ORA-00001: unique constraint (OT.SYS_C0010726) violated
Code language: SQL (Structured Query Language) (sql)

If you want to add a unique constraint for the company_nameand phone columns, you can use the following ALTER TABLE statement:

ALTER TABLE clients
ADD CONSTRAINT unique_company_phone UNIQUE(company_name, phone);
Code language: SQL (Structured Query Language) (sql)

As a result, the combination of values in the company_name and phone columns is unique among the rows in the clients table.

The following statement attempts to insert a new client with the company and phone that already exists:

INSERT INTO clients(first_name,last_name, email, company_name, phone)
VALUES('Sherly',    'Snider','[email protected]', 'ABC Inc', '408-875-6075');
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error message:

SQL Error: ORA-00001: unique constraint (OT.UNIQUE_COMPANY_PHONE) violated
Code language: SQL (Structured Query Language) (sql)

However, you can add the client who is in the company that already exists in the clients table but has a different phone:

INSERT INTO clients(first_name,last_name, email, company_name, phone)
VALUES('Sherly','Snider','[email protected]', 'ABC Inc', '408-875-6076');
Code language: SQL (Structured Query Language) (sql)

To disable the unique constraint UNIQUE_COMPANY_PHONE, you use the following statement:

ALTER TABLE clients
DISABLE CONSTRAINT unique_company_phone;
Code language: SQL (Structured Query Language) (sql)

And to enable it:

ALTER TABLE clients
ENABLE CONSTRAINT unique_company_phone;
Code language: SQL (Structured Query Language) (sql)

Or to drop it permanently:

ALTER TABLE clients
DROP CONSTRAINT unique_company_phone;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle unique constraint to ensure the data contained in a column or a group of columns is unique among the rows in the table.