Oracle Constraints

Oracle have different type of constraints. These constraints are like condition(rule)applied to column to maintain the integrity of Data.

Oracle has 6 predefined constraints.

R
Foreign Key (Refrence Constraint)
U
Unique
P
Primary Key
C
Check Constraints , Includes Not Null
O
WITH READ ONLY” constraint – Applied to Views
V
WITH CHECK OPTION” constraint – Applied to views

Details of constraints can be found in user_constraint table. Where constraint_type  column is used to define the constraint type and value of this column would be any of above option(R, U, P, C, O, V).


Unique :

Rules applied to Unique Column

  1. Duplicates are not allowed
  2. Can accept multiple Null value (Difference from Primary Key)
  3. Non Clustered Index will be created.(Default Behaviour)

Note: 1- More then one column can be unique.

Syntax :

CREATE TABLE table_name(column_name VARCHAR2(20) UNIQUE)
ALTER TABLE table_name ADD CONSTRAINT const_name UNIQUE(column)

 

Primary Key :

Rules applied to columns

  1. Duplicates are not allowed.
  2. Null values are not allowed.
  3. Clustered Index will be created(Default Behavior)

Note:

1- A table can have only one Primary key.

2- It is same as Unique and Not Null constraint- Note the difference.

Syntax:

CREATE TABLE table_name(column_name VARCHAR2(20) PRIMARY KEY)
ALTER TABLE table_name ADD CONSTRAINT  const_name PRIMARY KEY (column_name)

 

Not Null :

Rules applied to columns

  • Null values are not allowed.

Syntax :

CREATE TABLE table_name(column_name VARCHAR2(20) NOT NULL)

ALTER TABLE table_name MODIFY(column_name VARCHAR2(10) NOT NULL)

Note*:  Not Null is a column level constraint hence it can not be applied to table.


 

Check :

Rules applied to columns

  • check for certain condition(eg. empNo >1000)

Syntax :

CREATE TABLE table_name(column_name varchar2(20) check (column_name !='some_value'))
ALTER TABLE table_name ADD CONSTRAINT const_name CHECK(column_name != 'some_value')

 

Foreign Key :

Rules applied to columns

1-

 Syntax:

ALTER TABLE child_table_name ADD FOREIGN KEY (child_table_column_name) REFERENCES parent_table(parent_table_column_name) ON DELETE "CASCADE"/"SET NULL"  ENABLE NOVALIDATE

With Check Option Constraint

This Constraint applied to views only. This constraint is used for restricted DML.

 


With Read Only Constraint

This Constraint applied to views only. This constraint is used to allow only select against the view.

Note*: To know the difference between “WITH READ ONLY” & “WITH CHECK OPTION” constraint please visit here.

 


 Other Option For Constraint

There are other options available for these constraint.

  1. Validate  : Applied to all constraint
  2. onDelete : Applied only to Foreign Key
  3. Immediate & Deferred :
By | 2017-07-21T21:44:53+00:00 July 11th, 2012|Oracle|0 Comments

About the Author:

Leave A Comment