Domain Integrity
Domain integrity ensures that all the data items in a column fall within a defined set of valid values. Each column in a table has a defined set of values, such as the set of all numbers for Pubs..zip (five-digit), the set of all character strings for Pubs..au_lname, and the set of all dates for Sales..ord_date. When you limit the value assigned to an instance of that column (an attribute), you are enforcing domain integrity. Domain integrity enforcement can be as simple as choosing the correct data type and length for a column. In the Pubs database's titles table, the column PubDate has a datetime data type and is not nullable. The creator of the titles table chose a non-null condition and a datetime data type for this column. This choice ensures that a value is always present for the PubDate column, and that the value is a valid date, such as 11/22/99. In this way, the creator put in place some controls to maintain domain integrity.
The ANSI SQL-89 and SQL-92 standards define a CREATE DOMAIN statement, which Transact SQL (T-SQL) supports as user-defined data types (UDTs) combined with check constraints. The ANSI SQL-89/92 domain is derived from existing base data types, as in the following pseudocode example:
CREATE DOMAIN wholesale_price AS DECIMAL(5,2)
CONSTRAINT whsale_price_not_negative
CHECK (value >=0) NOT DEFERRABLE
You can create elementary domains in T-SQL by creating UDTs built on the base SQL Server data types. Also, you can add nullability options to a T-SQL UDT, as in the following example from the T-SQL online reference guide:
sp_addtype birthday, datetime, NULL
When you use this UDT in a CREATE or ALTER TABLE statement, you have to complete the domain integrity enforcement process by adding check constraints. A check constraint, applied at the time of table creation, enforces domain integrity by limiting the set of values that can be assigned to a column. Listing 4 contains an example of a check constraint.
DRI also is a form of domain integrity. In an enforced 1:M relationship, the domain of a foreign key and the domain of its corresponding primary key must be the same. In Listing 4, you can never insert a value for pub_id that doesn't already exist in the Publishers table. Thus, you've restricted the domain of title6.pub_id by the foreign-key reference.
Along this line, when you create a table specifically to restrict values in a column in another table (enforce domain integ-rity), the first table is called a reference, or lookup, table. The lookup table has a 1:M relationship with the table it modifies, and this relationship is always en-forced. For instance, in Listing 5, page 64, the column Pubs.type references the type column in a table called TypeTable. Listing 5 contains a description of the structure of the lookup table TypeTable and sample values. If you try to insert a row into Pubs..title7 and you use a value for type that isn't present in the TypeTable database, your insert operation fails. The DRI rule specified in Pubs..title7, coupled with the list of values in the TypeTable table, enforces domain integrity.
You can use constraints in T-SQL code to enforce domain integrity. For example, if you want to enforce the rule that a phone number is always null or a 10-digit value, you can use code similar to that in Listing 6.
Business Integrity
Business integrity, also called user integrity, ensures that the database enforces user-defined business rules, regulations, policies, and procedures. You usually enforce business integrity by using stored procedures and triggers. A stored procedure is a query that resides on the database server and processes rows and returns results. Triggers can enforce business integrity rules behind the scenes, because they fire without the user even realizing that they've been activated. Listing 7 contains an example of trigger code that enforces business integrity.
Integrity Counts
Data integrity is vital to a database. If the data values are questionable, the information derived from the data will be useless, or at least of greatly diminished value. The four database integrities are a set of rules that you can enforce in your database. Properly defined and applied, they work as a team to ensure that the data stored in your database is accurate and consistent.
End of Article
Prev. page
1
[2]
next page -->
