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

Query1.jpg

AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Query2.jpg

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

Query3.jpg

AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Query4.jpg

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

Query5.jpg

2. Check Constraint while using ALTER TABLE statement:

SYNTAX:

Query6.jpg

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

Query7.jpg

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

Query8.jpg

3. DROP A Check Constraint:

SYNTAX:
Query9.jpg

Example:


Query10.jpg

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:

Query11.jpg

AT SINGLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Query12.jpg

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

Query13.jpg

AT MULTIPLE COLUMN LEVEL

Method 1: With In-Line Specification/Column Level

Query141.jpg

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

Query151.jpg

2. Primary Key Constraint while using ALTER TABLE statement:

SYNTAX:

Query16.jpg

AT SINGLE COLUMN LEVEL

Query17.jpg

AT MULTIPLE COLUMN LEVEL

Query18.jpg

3. DROP A Primary Key Constraint:

SYNTAX


Query19.jpg
Example:

Query20.jpg