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 :