Discussion on:
View:
Show:
Do you implement constraints to reduce the number of data entry errors? What other methods do you use to try to ensure the integrity of your organization's data?
The proper checks can also be put into the programs that access the database. Pros and cons of doing this vis-a-vis using CHECK constraints: Program updates are generally easier to implement and cascade than DB updates (at least with the systems in my company, where we have separate DBs in numerous branches); however, DB-based constraints like CHECK protect against cases where a negligent programmer omits a needed data check.
The follow-up article suggested by Underground_In_TN would be very useful to tie up both methods, since it would teach you how to make a program check for error feedback from DB-based constraints. The most basic and critical constraints can then be put in the DB, and more application-specific constraints can be put in the programs accessing the DB.
The follow-up article suggested by Underground_In_TN would be very useful to tie up both methods, since it would teach you how to make a program check for error feedback from DB-based constraints. The most basic and critical constraints can then be put in the DB, and more application-specific constraints can be put in the programs accessing the DB.
What happens when you add a constraint for which there is already invalid data in the table?
As a starter, the question illustrates the need for good design up front. You are now in "always time to do it over, never time to do it right" mode.
There are several ways to handle this, it seems to me - none of them pretty. You will need to extract the data out to a holding table, drop and re-create the existing table with the constraint then attempt to insert each new row. If the insert fails you need to catch the exception. You can then do one of several things. First, you can try getting a valid postal code from any one of a number of commercially available applications - this will probably still leave some invalid zips as none of these systems are 100% accurate; second you can try to resolve this by hand, having a business person go through each line; third, you can just dump the bad records and hope your customers come back.
You should also hire someone who understands DB design.
There are several ways to handle this, it seems to me - none of them pretty. You will need to extract the data out to a holding table, drop and re-create the existing table with the constraint then attempt to insert each new row. If the insert fails you need to catch the exception. You can then do one of several things. First, you can try getting a valid postal code from any one of a number of commercially available applications - this will probably still leave some invalid zips as none of these systems are 100% accurate; second you can try to resolve this by hand, having a business person go through each line; third, you can just dump the bad records and hope your customers come back.
You should also hire someone who understands DB design.
If you want to ignore the existing data when applying a check constraint, use the NOCHECK option. See the books online for more information.
Since data doesn't just magically appear in a database, but is put there by an external application or a stored procedure called by an external application, a great follow-up article to this would be how to write such programs to handle the exceptions/errors raised when it tries to write invalid data into a field with a CHECK constraint.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
