Page 80 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 5: Integrity Constraints

Besides the cell name, cell length and cell data type, there are other parameters i.e. other data Notes
constraints that can be passed to the DBA at cell creation time.
These data constraints will be connected to a cell by the DBA as flags. Whenever a user attempts
to load a cell with data, the DBA will check the data being loaded into the cell against the data
constraints defined at the time the cell was created. If the data being loaded fails any of the data
constraint checks fired by the DBA, the DBA will not load the data into the cell, reject the entered
record, and will flash an error message to the user.

These constraints are given a constraint name and the DBA stores the constraints with its name
and instructions internally along with the cell itself.
The constraint can either be placed at the column level or at the table level.

Column Level Constraints: If the constraints are defined along with the column definition, it is
called as a column level constraint. Column level constraint can be applied to anyone column at
a time i.e. they are local to a specific column. If the constraint spans across multiple columns, the
user will have to use table level constraints.
Table Level Constraints: If the data constraint attached to a specific cell in a table references the
contents of another cell in the table then the user will have to use table level constraints. Table
level constraints are stored as a part of the global table definition.

NULL Value Concepts

While creating tables, if a row lacks a data value for a particular column, that value is said to be
null. Columns of any data types may contain null values unless the column was defined as not
null when the table was created.

Principles of NULL Values

1. Setting a null value is appropriate when the actual value is unknown, or when a value
would not be meaningful.
2. A null value is not equivalent to a value of zero.
3. A null value will evaluate to null in any expression. e.g. null multiplied by 10 is null.

4. When a column name is defined as not null, then that column becomes a mandatory
column. It implies that the user is forced to enter data into that column.

Example: Create table client master with a not null constraint on columns client no,
Name, address, address2.
NOT NULL as a column constraint:
CREATE TABLE client master
(client_no varchar2(6) NOT NULL,
name varchar2(20) NOT NULL,
address 1 varchar2(30) NOT NULL,
address2 varchar2(30) NOT NULL,
city varchar2(15), state varchar2(15), pin code number( 6),
remarks varchar2(60), bal_due number (10,2));

LOVELY PROFESSIONAL UNIVERSITY 73