DBA Call to Action: Oracle Database Integrity

September 25, 2003

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers