Oracle / PLSQL: Unique Constraints - TechOnTheNet

1. Home
2. Oracle / PLSQL

Oracle / PLSQL: Unique Constraints

This Oracle tutorial explains how to create, drop, disable, and enable unique constraints in Oracle with syntax and examples.

What is a unique constraint in Oracle?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

Note

• In Oracle, a unique constraint can not contain more than 32 columns.
• A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

What is the difference between a unique constraint and a primary key?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Oracle does not permit you to create both a primary key and unique constraint with the same columns.

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in Oracle is:

CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n) ); table_name The name of the table that you wish to create. column1, column2 The columns that you wish to create in the table. constraint_name The name of the unique constraint. uc_col1, uc_col2, ... uc_col_n The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in Oracle using the CREATE TABLE statement.

CREATE TABLE supplier ( supplier_id numeric(10) NOT NULL, supplier_name varchar2(50) NOT NULL, contact_name varchar2(50), CONSTRAINT supplier_unique UNIQUE (supplier_id) );

In this example, we've created a unique constraint on the supplier table called supplier_unique. It consists of only one field - the supplier_id field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE supplier ( supplier_id numeric(10) NOT NULL, supplier_name varchar2(50) NOT NULL, contact_name varchar2(50), CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name) );

Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in Oracle is:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name The name of the table to modify. This is the table that you wish to add a unique constraint to. constraint_name The name of the unique constraint. column1, column2, ... column_n The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in Oracle using the ALTER TABLE statement.

ALTER TABLE supplier ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);

In this example, we've created a unique constraint on the existing supplier table called supplier_unique. It consists of the field called supplier_id.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE supplier ADD CONSTRAINT supplier_name_unique UNIQUE (supplier_id, supplier_name);

Drop Unique Constraint

The syntax for dropping a unique constraint in Oracle is:

ALTER TABLE table_name DROP CONSTRAINT constraint_name; table_name The name of the table to modify. This is the table that you wish to remove the unique constraint from. constraint_name The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in Oracle.

ALTER TABLE supplier DROP CONSTRAINT supplier_unique;

In this example, we're dropping a unique constraint on the supplier table called supplier_unique.

Disable Unique Constraint

The syntax for disabling a unique constraint in Oracle is:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; table_name The name of the table to modify. This is the table whose unique constraint you wish to disable. constraint_name The name of the unique constraint to disable.

Example

Let's look at an example of how to disable a unique constraint in Oracle.

ALTER TABLE supplier DISABLE CONSTRAINT supplier_unique;

In this example, we're disabling a unique constraint on the supplier table called supplier_unique.

Enable Unique Constraint

The syntax for enabling a unique constraint in Oracle is:

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name; table_name The name of the table to modify. This is the table whose unique constraint you wish to enable. constraint_name The name of the unique constraint to enable.

Example

Let's look at an example of how to enable a unique constraint in Oracle.

ALTER TABLE supplier ENABLE CONSTRAINT supplier_unique;

In this example, we're enabling a unique constraint on the supplier table called supplier_unique.

NEXT: Check Constraints Share on:

Databases

• SQL
• Oracle / PLSQL
• SQL Server
• MySQL
• MariaDB
• PostgreSQL
• SQLite

MS Office

• Excel
• Access
• Word

Web Development

• HTML
• CSS
• JavaScript
• Color Picker

Programming

• C Language

More

• ASCII
• Unicode
• Linux
• UNIX
• Techie Humor

Oracle Basics

• ALIASES
• AND
• AND & OR
• BETWEEN
• COMPARISON OPERATORS
• DELETE
• DISTINCT
• EXISTS
• FROM
• GROUP BY
• HAVING
• IN
• INSERT
• INSERT ALL
• INTERSECT
• IS NOT NULL
• IS NULL
• JOIN
• LIKE
• MINUS
• NOT
• OR
• ORDER BY
• PIVOT
• REGEXP_LIKE
• SELECT
• SUBQUERY
• TRUNCATE
• UNION
• UNION ALL
• UPDATE
• WHERE

Oracle Advanced

• Alter Table
• Alter Tablespace
• Change Password
• Check Constraints
• Comments in SQL
• Create Schema
• Create Schema Statement
• Create Table
• Create Table As
• Create Tablespace
• Create User
• Data Types
• Declare Variables
• Drop Table
• Drop Tablespace
• Drop User
• Error Messages
• Find Default Tablespace
• Find Users
• Find Users Logged In
• Find Version Information
• Functions
• Global Temporary
• Grant/Revoke Privileges
• Indexes
• Literals
• Local Temporary
• Primary Keys
• Procedures
• Roles
• Sequences
• Set Default Tablespace
• Synonyms
• System Tables
• Unique Constraints
• Views

Oracle Cursors

• Close Cursor
• Cursor Attributes
• Declare Cursor
• Fetch Cursor
• Open Cursor
• Select For Update
• Where Current Of

Oracle Exception Handling

• Named Programmer-Defined Exception
• Named System Exception
• WHEN OTHERS Clause
• SQLCODE
• SQLERRM

Oracle Foreign Keys

• Disable Foreign Key
• Drop Foreign Key
• Enable Foreign Key
• Foreign Key
• Foreign Key (cascade delete)
• Foreign Key (set null delete)

Oracle Loops/Conditionals

• CURSOR FOR LOOP
• EXIT
• FOR LOOP
• GOTO
• IF-THEN-ELSE
• LOOP
• REPEAT UNTIL LOOP
• WHILE LOOP

Oracle Transactions

• Commit Transaction
• Lock Table
• Rollback Transaction
• Set Transaction

Oracle Triggers

• After Delete Trigger
• After Insert Trigger
• After Update Trigger
• Before Delete Trigger
• Before Insert Trigger
• Before Update Trigger
• Disable All Triggers
• Disable Trigger
• Drop Trigger
• Enable All Triggers
• Enable Trigger

String/Char Functions

• ASCII
• ASCIISTR
• CHR
• COMPOSE
• CONCAT
• Concat with ||
• CONVERT
• DECOMPOSE
• DUMP
• INITCAP
• INSTR
• INSTR2
• INSTR4
• INSTRB
• INSTRC
• LENGTH
• LENGTH2
• LENGTH4
• LENGTHB
• LENGTHC
• LOWER
• LPAD
• LTRIM
• NCHR
• REGEXP_INSTR
• REGEXP_REPLACE
• REGEXP_SUBSTR
• REPLACE
• RPAD
• RTRIM
• SOUNDEX
• SUBSTR
• TRANSLATE
• TRIM
• UPPER
• VSIZE

Numeric/Math Functions

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• AVG
• BITAND
• CEIL
• COS
• COSH
• COUNT
• EXP
• FLOOR
• GREATEST
• LEAST
• LN
• LOG
• MAX
• MEDIAN
• MIN
• MOD
• POWER
• REGEXP_COUNT
• REMAINDER
• ROUND (numbers)
• ROWNUM
• SIGN
• SIN
• SINH
• SQRT
• SUM
• TAN
• TANH
• TRUNC (numbers)

Date/Time Functions

• ADD_MONTHS
• CURRENT_DATE
• CURRENT_TIMESTAMP
• DBTIMEZONE
• EXTRACT
• LAST_DAY
• LOCALTIMESTAMP
• MONTHS_BETWEEN
• NEW_TIME
• NEXT_DAY
• ROUND (dates)
• SESSIONTIMEZONE
• SYSDATE
• SYSTIMESTAMP
• TRUNC (dates)
• TZ_OFFSET

Conversion Functions

• BIN_TO_NUM
• CAST
• CHARTOROWID
• FROM_TZ
• HEXTORAW
• NUMTODSINTERVAL
• NUMTOYMINTERVAL
• RAWTOHEX
• TO_CHAR
• TO_CLOB
• TO_DATE
• TO_DSINTERVAL
• TO_LOB
• TO_MULTI_BYTE
• TO_NCLOB
• TO_NUMBER
• TO_SINGLE_BYTE
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL

Analytic Functions

• CORR
• COVAR_POP
• COVAR_SAMP
• CUME_DIST
• DENSE_RANK
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• LISTAGG
• NTH_VALUE
• RANK
• STDDEV
• VAR_POP
• VAR_SAMP
• VARIANCE

Advanced Functions

• BFILENAME
• CARDINALITY
• CASE
• COALESCE
• DECODE
• EMPTY_BLOB
• EMPTY_CLOB
• GROUP_ID
• LNNVL
• NANVL
• NULLIF
• NVL
• NVL2
• SYS_CONTEXT
• UID
• USER
• USERENV

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy.

Copyright © 2003-2025 TechOnTheNet.com. All rights reserved.