Ensures Unique Data Contained In A Column - Oracle Tutorial

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.

Send Cancel