How to Maintain Domain Integrity in a Multitable SQL Database - dummies

How to Maintain Domain Integrity in a Multitable SQL Database

By Allen G. Taylor

You usually can’t guarantee that a particular data item in a SQL database is correct, but you can determine whether a data item is valid. Many data items have a limited number of possible values. If you make an entry that is not one of the possible values, that entry must be an error.

The United States, for example, has 50 states plus the District of Columbia, Puerto Rico, and a few possessions. Each of these areas has a two-character code that the U.S. Postal Service recognizes. If your database has a State column, you can enforce domain integrity by requiring that any entry into that column be one of the recognized two-character codes.

If an operator enters a code that’s not on the list of valid codes, that entry breaches domain integrity. If you test for domain integrity, you can refuse to accept any operation that causes such a breach.

Domain integrity concerns arise if you add new data to a table by using either the INSERT statement or the UPDATE statement. You can specify a domain for a column by using a CREATE DOMAIN statement before you use that column in a CREATE TABLE statement, as shown in the following example, which creates a table for major league baseball teams:

 CHECK (VALUE IN ('American', 'National'));
 TeamName   CHAR (20)   NOT NULL,
 League   LeagueDom   NOT NULL
 ) ;

The domain of the League column includes only two valid values: American and National. Your DBMS doesn’t enable you to commit an entry or update to the TEAM table unless the League column of the row you’re adding has a value of either ‘American’ or ‘National’.