DBA Call to Action: Oracle Database Integrity

Integrity

1.     
Steadfast adherence to a strict moral or ethical code.

2.      The
state of being unimpaired; soundness.

3.      The
quality or condition of being whole or undivided; completeness.

Source: The American Heritage. Dictionary of the
English Language, Fourth Edition

Copyright ) 2000 by Houghton Mifflin Company.

The data within our
databases must constantly follow the rules and constraints placed within the
data models. Without constraints, the data would hold no meaning. The
reliability and integrity of that data would always be in question and the
users of such data would always question the validity of it. If a database were
to neglect the rules and overstep the boundaries that were placed upon it,
there would be mayhem within the database and that database would cease to
exist for any value.

Database Integrity Constraints

NOT NULL

Within the definition of a
table, we at times might allow data to contain a NULL value. This NULL value is
not really a value at all and is considered to be an absence of value. The
constraint of NOT NULL forces a value to be given to a column.

Unique Key

Uniqueness for a column or
set of columns means that the values in that column or set of columns must be
different from all other columns or set of columns in that table. The unique
key will stand on its’ own and has power to drive other information in the
database through foreign keys. A unique key may contain NULL values since they
are by definition a unique non-valued value.

Primary Key Values

Primary key values are
much like unique keys except that they are designed to uniquely identify a row
in a table. They can consist of a single column or multiple columns. The
primary key cannot contain NULL values.

CHECK Constraint

You can put on a column
an integrity constraint that requires certain conditions to be met before the
data is inserted or modified. If the checks are not satisfied then the
transaction is not allowed to finish.

Relationships

Any table in the database that
has a primary key or unique key can be referenced by another table by setting
up a rule that relates those tables and governs the relationship. In this
relationship, there is what is known as a parent table and a child table. The
child table uses a foreign key to reference the parent table’s primary key or
unique key. Through the use of the relationship both parent tables and child
tables can effect each other is positive and negative ways.

Types of Relationships

Restrict or No Action

If there is an attempt to
alter or delete data in the parent table and there are rows in the child table,
the transaction is not allowed.

Set to Null

If a delete where to
happen on the parent table, the columns for the rows in the child table that
were referencing the parent would be set to NULL.

Set to Default

When a delete or
modification to the parent table breaks the relationship for the child table,
the child’s table columns are set to a predefined default value.

Cascade

If the parent data is
modified, then all the children’s table data is also modified. If the parent is
deleted then all the children are deleted.

Database integrity is the thread that holds database objects
together to satisfy business objectives and rules. It is unfortunate that data
integrity doesn’t always cascade into the real world and hold us all together
when times get tough.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles