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 (
Similarly in alter table command
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 (
create table EMP_DETL (
alter table EMP add constraint EMP_UQ unique (emp_id);
The first example illustrates a column constraint and the second a table constraint.
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 (
create table EMP_DETL (
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
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 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
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 |
“Amazon and the Amazon logo are trademarks of Amazon.com, Inc. or its affiliates.”Copyright - © 2004 - 2019 - All Rights Reserved.