Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle Unique Constraint

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 ... );

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) );

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 ... );

or with out-of-line constraint syntax:

CREATE TABLE table_name ( ... column_name data_type, ..., CONSTRAINT unique_constraint_name UNIQUE(column_name) );

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) );

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);

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

ALTER TABLE table_name DISABLE CONSTRAINT unique_constraint_name;

And then enable it:

ALTER TABLE table_name ENABLE CONSTRAINT unique_constraint_name;

Or even drop a unique constraint:

ALTER TABLE table_name DROP CONSTRAINT unique_constraint_name;

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) );

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

The following statement inserts a row into the clients table:

INSERT INTO clients(first_name,last_name, email, company_name, phone) VALUES('Christene','Snider','christene.snider@abc.com', 'ABC Inc', '408-875-6075');

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','christene.snider@abc.com', 'ABC Inc', '408-875-6076');

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

SQL Error: ORA-00001: unique constraint (OT.SYS_C0010726) violated

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);

As the 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 exist:

INSERT INTO clients(first_name,last_name, email, company_name, phone) VALUES('Sherly', 'Snider','sherly.snider@abc.com', 'ABC Inc', '408-875-6075');

Oracle issued the following error message:

SQL Error: ORA-00001: unique constraint (OT.UNIQUE_COMPANY_PHONE) violated

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','sherly.snider@abc.com', 'ABC Inc', '408-875-6076');

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

ALTER TABLE clients DISABLE CONSTRAINT unique_company_phone;

And to enable it:

ALTER TABLE clients ENABLE CONSTRAINT unique_company_phone;

Or to drop it permanently:

ALTER TABLE clients DROP CONSTRAINT unique_company_phone;

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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Check Constraint
Next Oracle NOT NULL

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.