Specifying Constraints while creating table in Oracle10g to enhance Data Integrity

Programmers can define and enforce the data quality standards that the data in the database needs to meet, based on the business rules. One way of doing is by doing checks in the application program. Another way is while creating a table in SQL and SQL*Plus, they can specify constraints to enforce data integrity in the database.

The constraint can be for a single column or group of columns of a table. The different types of constraints on a table that can be specified are Not Null, Candidate key, Primary key, Foreign key and Check constraints. The correct number and type of constraints have to be provided so there is a balance between the work done in applications to maintain data and the time oracle takes to do updates in data.

The constraints can be specified either as a column constraint which is given as part of the column definition or table constraint where it is given at the end of the create table clause. Further details are given below.

The Not Null constraint

The default value of a column in a table is null. It is possible to specify a column to be not null as shown below in create table command.

create table EMP (
emp_id number(5) not null,
emp_name varchar2(200));

Similarly in alter table command
alter table EMP modify emp_id not null;

Candidate key/ Unique key

The Candidate key constraint is also known as unique key constraint. Unique constraints ensure that every value in the specified key is unique by not allowing duplicate values. It can be a combination of one or more columns and can be given both as a part of create table or alter table command.

create table EMP (
emp_id number(5) unique,
emp_name varchar2(200));

create table EMP_DETL (
emp_id number(5),
emp_ssno varchar2(200),
emp_addr varchar2(500),
constraint EMP_UQ unique (emp_id,emp_ssno));

alter table EMP add constraint EMP_UQ unique (emp_id);

The first example illustrates a column constraint and the second a table constraint.
When a unique constraint is created oracle will create a corresponding unique index.

Primary key constraint

A primary key is a combination of a unique and not null constraint. There can be only one primary key constraint. It can be specified as a column constraint if it is a single column primary key or as a table constraint if it encompasses multiple columns.

create table emp (
emp_id number(5) primary key,
emp_name varchar2(200));

create table EMP_DETL (
emp_id number(5),
emp_ssno varchar2(200),
emp_addr varchar2(500),
constraint EMP_UQ primary key (emp_id,emp_ssno));

Here primary key is explicitly named. If name is not specified, oracle will generate a name.

Foreign key Constraint

A foreign key constraint is also called as referential integrity constraint. This is a combination of columns with values that is based on the primary key values of another table.

create table EMP_DETL
( emp_id number(5),
emp_dept varchar2(10),
emp_location varchar2(30),
constraint EMP_FK foreign key (emp_id)
references EMP (emp_id));

In the EMP_DETL table the emp_id refers to values of the emp_id column in EMP table. You cannot refer to a primary and unique key of the same table. However you can refer to a table in a remote database in the references clause.
The On delete cascade added to reference clause will let oracle know whether to delete the dependent row when the parent table’s corresponding row is deleted. This helps maintains referential integrity.

The Check Constraint

This is used to specify the values in column to be in a certain range or to state a minimum requirement for value.

create table EMP_SAL
( emp_id number(3),
salary number(5) check (salary >=0));

alter table EMP_SAL add constraint CHECK_SAL check (salary >=0);

Using any of above constraints, it is ensured that whenever commands are issued in SQL that alters data in a table, oracle will check and validate automatically if the new data satisfies the integrity constraints specified. Thus by defining constraints, programmers are saved from the hassle of introducing checks in the programs to make sure that data adheres to business rules.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |