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.
Syntax
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.
Syntax
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 Constraint | Level | Role |
| PRIMARY KEY | Column Level, Table Level | Enforces Existential Integrity. Uniquely identifies data rows by restricting NULL and duplicate values |
| NOT NULL | Column Level | Restricts NULL values |
| UNIQUE | Column Level, Table Level | Restricts duplicate values |
| CHECK | Column Level, Table Level | Used for data validation so that only specific values can be allowed in a column or set of columns |
| FOREIGN KEY | Column Level, Table Level | Enforces referential Integrity. Used for data validation so that column(s) can store only those values available in the primary key of the referenced table. |