Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 25, 2003

DBA Call to Action: Oracle Database Integrity

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date