How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
How to Implement a Database in MySQL for HTML5 and CSS3 Programming

How to Maintain Domain Integrity in a Multitable SQL Database

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:

CREATE DOMAIN LeagueDom CHAR (8)
 CHECK (VALUE IN ('American', 'National'));
CREATE TABLE TEAM (
 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’.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Use SQL with Microsoft Access
How to Handle SQL Conditions
How to Use Modifying Clauses in SQL
PHP & MySQL Web Development AIO For Dummies Cheat Sheet
How to Use the SQL UNION Operator
Advertisement

Inside Dummies.com