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.

FOREIGN KEYS

This Oracle tutorial explains how to use Foreign Keys in Oracle with syntax and examples.

WHAT IS A FOREIGN KEY IN ORACLE?

A foreign key is a way to enforce referential integrity within your Oracle database. A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

USING A CREATE TABLE STATEMENT

Syntax

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

Example

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

We could also create a foreign key 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_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
);

In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.

USING AN ALTER TABLE STATEMENT

Syntax

The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n);

Example

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);

In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.

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

ALTER TABLE products
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);

PRIMARY KEYS

This Oracle tutorial explains how to create, drop, disable, and enable a primary key in Oracle with syntax and examples.

WHAT IS A PRIMARY KEY IN ORACLE?

In Oracle, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

NOTE

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

CREATE PRIMARY KEY - USING CREATE TABLE STATEMENT

You can create a primary key in Oracle with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in Oracle:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.

We could also create a primary key 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_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE PRIMARY KEY - USING ALTER TABLE STATEMENT

You can create a primary key in Oracle with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

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

ALTER TABLE supplier
ADD CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

DROP PRIMARY KEY

You can drop a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DROP CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

DISABLE PRIMARY KEY

You can disable a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to disable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

Let's look at an example of how to disable a primary using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

ENABLE PRIMARY KEY

You can enable a primary key in Oracle using the ALTER TABLE statement.

Syntax

The syntax to enable a primary key using the ALTER TABLE statement in Oracle/PLSQL is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

Let's look at an example of how to enable a primary key using the ALTER TABLE statement in Oracle.

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called supplier_pk.

Using the LIKE Condition

Use the LIKE condition to perform wildcard searches of valid search string values.

Search conditions can contain either literal characters or numbers:

–    % denotes zero or many characters.

–    _ denotes one character.

SELECT       first_name

FROM         employees

WHERE        first_name LIKE 'S%' ;

Using the IN Condition

Use the IN membership condition to test for values in a list:

SELECT employee_id, last_name, salary, manager_id

FROM   employees

WHERE  manager_id IN (100, 101, 201) ;

EMPLOYEE_ID	LAST_NAME	SALARY	MANAGER_ID
201	Edi	1000	100
202	Laki	2000	100
203	John	1500	101
204	Haan	3000	201

Using Comparison Conditions

SELECT last_name, salary

FROM   employees

WHERE  salary <= 3000 ;

Last_name	Salary
Edin	2400
John	2300

Using the BETWEEN Condition

Use the BETWEEN condition to display rows based on a range of values:

SELECT last_name, salary

FROM   employees

WHERE  salary BETWEEN 2500 AND 3500 ;

Last_name	Salary
Edin	2400
John	2300

Comparison Conditions

Operator	Meaning
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to
BETWEEN ...AND...	Between two values (inclusive)
IN(set)	Match any of a list of values
LIKE	Match a character pattern
IS NULL	Is a null value