Constraints in Oracle : Part 1

This article explains the Constraint clause in Oracle that is basically used to apply the specific rules to the data that restrict the values/data entered into the database columns. Constraints help to minimize the amount of validation done at the application level and can be specified at the time of creation or after the creation of the table. Constraints can be defined at a specific column level called an In Line Specification and also at the table level known as an Out Of Line Specification that we will further explain in detail.

In Oracle Constraints are divided into the following six parts:

1. Check Constraints (CC)

2. Primary Key Constraints (PK)

3. Foreign Key Constraints (FK)

4. Unique Key Constraints (UK)

5. Not Null Constraints (NN)

Before deliberating them, let me share that the constraints clause is used during the declaration of the following statements:

• CREATE TABLE

• CREATE VIEW

• ALTER TABLE

• ALTER VIEW

Now, we will understand these constraints types in detail.

For all the examples shown below we assumed the following table:

CUSTOMER { Cust_ID, Cust_Name, Age, ContactNo, Gender, Address}

i) CHECK CONSTRAINTS (CC)

The Check constraint is the most commonly used constraint from all the other constraints that will monitor the physical data. The data present in the column should meet the specified condition. Constraints can be created including single or multiple columns and conditions.

Example:

1. Check Constraint while using CREATE TABLE statement:

SYNTAX



AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level



Method 2: With Out-Of-Line Specification/Table Level



AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level



Method 2: With Out-Of-Line Specification/Table Level



2. Check Constraint while using ALTER TABLE statement:

SYNTAX:



AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Not allowed in Check Constraint.

Method 2: With Out-Of-Line Specification/Table Level



AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Not allowed in Check Constraint.

Method 2: With Out-Of-Line Specification/Table Level



3. DROP A Check Constraint:

SYNTAX:


Example:



ii) PRIMARY KEY CONSTRAINT (PK)

A Primary Key constraint prohibits multiple rows from having the same value in the same column. In other words, it restricts the duplicity of records in a table. The Primary key is the key that is always unique and never NULL, there must be only one primary key per table and it can be created from composite keys also. In Oracle the Primary key cannot have more than 32 columns. For example no two customers would have the same ID number.

Example:

1. Primary Key Constraint while using CREATE TABLE statement:

SYNTAX:



AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level



Method 2: With Out-Of-Line Specification/Table Level



AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level



Method 2: With Out-Of-Line Specification/Table Level



2. Primary Key Constraint while using ALTER TABLE statement:

SYNTAX:



AT SINGLE COLUMN LEVEL



AT MULTIPLE COLUMN LEVEL



3. DROP A Primary Key Constraint:

SYNTAX


Example: