SQL- Type of Constraints in Oracle

Constraints are the restrictions implemented on the tables that force the users to insert only those values that satisfy the constraints. This is actually a method to implement data consistency and allow data insertion or update according to the business rules of the organization. In this tutorial you will learn about what are the different type of constraints in Oracle.

Type of Constraints in Oracle

Oracle gives the flexibility to add columns on single column or multiple columns. Type of Constraints in Oracle are categorized according to where the constraints are defined while creating a table.

Column Level Constraint

If you want the constraint on a single column like unique email ID or SSN Number, it must be the Column Level Constraint.  A column Level constraint can be applied while defining a column. It cannot be added after the column is defined. It means the current column you are creating can be used to create a constraint. You can always add a constraint using ALTER TABLE command if you forget while creating the table.


Column-Name Datatype(size) [CONSTRAINT constraint-name] Constraint Type,

Table Level Constraint

If you want multiple columns to fulfill a condition in combination, then you will need a Table Level Constraint. For example allowing only unique address values may need table level constraint if you are storing address in different columns like house number, street, city and zip code.

A table level constraint can only be created after the definition of table is complete. It can be defined after the last column of the table is defined. The reason is simple that if you are applying a constraint on multiple columns of the table all the column definitions must be already available.


CREATE TABLE table-name
column-name1   Datatype (length) [DEFAULT Value/Expression][ Column Constraint],
column-name2   Datatype (length) [DEFAULT Value/Expression][ Column Constraint],
column-name3   Datatype (length) [DEFAULT Value/Expression][ Column Constraint],
column-nameN  Datatype (length) [DEFAULT Value/Expression][ Column Constraint],
CONSTRAINT constraint-name Constaint type (column-name [, column-name,…])

Constraints Types

Name of ConstraintLevelRole
PRIMARY KEYColumn Level, Table LevelEnforces Existential Integrity. Uniquely identifies data rows by restricting NULL and duplicate values
NOT NULLColumn LevelRestricts NULL values
UNIQUEColumn Level, Table LevelRestricts duplicate values
CHECKColumn Level, Table LevelUsed for data validation so that only specific values can be allowed in a column or set of columns
FOREIGN KEYColumn Level, Table LevelEnforces referential Integrity. Used for data validation so that column(s) can store only those values available in the primary key of the referenced table.