Oracle Concepts: Data Integrity Rules I'm reading through the Concepts manual again as mentioned on last week.

I'm going to make a small effort to post some of the key concepts here over the next couple of weeks. If you've read through the Concepts Guide before, this can serve as a brief refresher. If not, good, you're exposed to something new.

Data Integrity Rules

This section describes the rules that can be applied to table columns to enforce different types of data integrity.

Null rule: A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.

Unique column values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).

Primary key values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.

Referential integrity rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).

Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:

* Restrict: Disallows the update or deletion of referenced data.
* Set to null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
* Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
* Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
* No action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle Database uses No Action as its default action.)

Complex integrity checking: A user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).

Reading on past the brief section to the Constraint States I found this nugget:

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

This is a great tool for legacy systems. You have data in the column(s) that you can't really do anything with, but you want to insure that all future data that goes in that particular column(s) matches the parent key.

Of course the ideal is to somehow clean the data up, but you don't always have that option. This is a good first step towards to overall cleanup of your legacy system.

Labels: database, documentation, oracle

  ¶ 6/29/2009 01:17:00 AM