5. Populating Tables : Successful Use of the Copy Statement : How to Check for Integrity Errors
 
Share this page                  
How to Check for Integrity Errors
When you use the COPY statement, the data being copied is not checked for integrity errors.
To check the integrity of your data before or after using the COPY statement, follow these steps:
1. Use the CREATE INTEGRITY statement (or the equivalent feature in VDBA) to impose the integrity constraint. For example:
CREATE INTEGRITY ON personnel
    IS name LIKE '\[A-Z\]%' escape '\';
If the search condition is not true for every row in the table, an error message is returned and the integrity constraint is rejected.
An ALTER TABLE ADD CONSTRAINT statement that adds a check constraint can be used in place of the CREATE INTEGRITY. If a permanent integrity constraint is not required, a simple select as in step 2 can be used to verify data integrity.
2. If the integrity constraint is rejected, find the incorrect rows; for example:
SELECT name FROM personnel 
    WHERE name NOT LIKE '\[A-Z\]%' escape '\';
3. For small tables, you can use Query-By-Forms to quickly scan the table and correct the errors.
For more information on integrity checking and integrity constraints, see the chapter “Ensuring Data Integrity.”