Constraints In Oracle with examples

In Oracle constraints are critical to the flexibility,scalability  and integrity of your database.Constraints can be defined at the time of table creation or after creation of the table.It is used to prevent invalid data entry into tables.All constraints are stored in the data dictionary. You can define constraint at the column level or table level.

Use of Constraints:

• Prevent the deletion of table if there are dependencies from other tables
• Provides rules for oracle tools such as Oracle developer
• Enforce rules on the data in a table whenever a row is inserted,updated or deleted from that table

There are number of different kind of constraints that you will be concerned with Oracle Server. They are
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4.FOREIGN KEY
5. CHECK

NOT NULL:

This constraint must be defined at column level.These are in-line constraints that indicate that a column can not contain null values.If you declare columns without NOT NULL constraint can contain null values by default.If constraints apply that more than one column must be defined at the table level.

Example:

Column level constraint:

CREATE TABLE Employee( emp_id Number(6),FirstName varchar2(20) NOT NULL,phoneno number(12),Hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,job_id Number(6),salary Number(8,2), Dept_id Number(6));

I have created Employee table,in this table FirstName and Hire_date  columns contains NOT NULL constraints.That means a column can not be  null value. NOT NULL constraint must be defined at the column level.

UNIQUE:

Unique constraints are like alternative primary key constraints that means now two rows of a table can have duplicate values in a specified column or set of columns.
If the UNIQUE constraint  comprises more than one column,that group column is called a composite UNIQUE key. 

Example:

Defined at either table level or column level:

CREATE TABLE Employee(Emp_id Number(6),FirstName varchar2(20) NOT NULL ,Hire_date DATE NOT NULL,job_id Number(6),  Salary number(8,2),Dept_id number(6) constraint emp_id_UK UNIQUE(EMP_ID)); 

UNIQUE Constrains can be defined at the column level or table level. The composite UNIQUE key is created by  using the table level definition.

In the above example we had applies UNIQUE constraint to the emp_id column of the employee table.The name of the constraint is emp_id_UK.

PRIMARY KEY:

A PRIMARY KEY constraint creates a primary key for the table.Only one primary key can be crated for each row in a table.This primary key constraint is a column or set of columns that uniquely identifies  each row in a table.


Example:

Create table Department(Dept_id number(6),dept_name varchar2(20) constraint dept_name_nn NOT NULL,location_id number(6),constraint dept_id pk primary key(dept_id));

A table can have only one primary key but can have several UNIQUE constraints.
In the above example Dept_id column is the primary key that means uniqueness of each row in a table.

FOREIGN KEY:

A Foreign Key represent relationships between tables.It is a column or group of columns whose values are derived from the primary key or unique key.Foriegn key is column that references a columns of a table and it can be the same table also. A composite foreign key on attributes should be declared at table level or out of line.

• FOREIGN KEY identifies the column or combination of columns in the child table that makes up of the foreign key.
• REFERENCES identifies the parent table and the column or combination of columns that make up the referenced key.

From the above two tables such as employee and department we will apply foreign key like below:

Create table Employee(Emp_id number(5),FirstName varchar2(20) NOT NULL, Hire_date DATE NOT NULL, JOB_ID number(4),salary number(8,2),Dept_id number(4),constraint emp_dept_fk FOREIGN KEY(Dept_id) REFERENCES Department((Dept_id),constraint emp_id_uk UNIQUE(emp_id));

• The foreign key is defined in child table and table contains the referenced column is the parent table.

CHECK:

The check constraint defines a condition that each row must satisfy. A single column can have multiple check constraints  that refer to the column in its definition.There is no limit to the check constraint in the column level.

• The CHECK constraint can be defined at column level or table level
• There is no limit to the number check constraints that can be defined on a column

Restrictions:

• Calls to functions SYSDATE,UID,USER,USERVIEW
• Date constant that are not fully satisified

Example:

Create table employee(

.........................................

salary number(8,2) constraint emp_salary_min CHECK(salary>0)

Read also : Data types in Oracle with examples