DBA Call to Action: Oracle Database Integrity
September 25, 2003
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
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.
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.
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.
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.
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.