Data validation is often a topic of great importance when it comes to databases. Since information is constantly being updated, deleted, queried, or moved around, having valid data is a must. By practicing simple data validation rules, databases are more consistent, functional, and provide more value to their users.
When using SQL, data validation is the aspect of a database that keeps data consistent. The key factors in data integrity are constraints, referential integrity and the delete and update options. The main types of constraints in SQL are check, unique, not null, and primary constraints. Check constraints are used to make certain that a statement about the data is true for all rows in a table. The unique constraint ensures that no two rows have the same values in their columns. The not null constraint is placed on a column and states that data is required in that column. However, in SQL, the not null constraint can only be placed on a single column. Finally, the primary key constraint is a mixture of the unique constraint and the not null constraint meaning the no two rows can have the same values in their columns and that a column must have data.
Referential integrity is a key aspect in data integrity that is usually associated with two tables; the lookup table and the data table. Typically, referential integrity is applied when data is inserted, deleted, or updated. The inserts and updates to the data table prevented by referential integrity happen in the foreign key column. Referential integrity will prevent inputting data in the foreign key column that is not listed in the lookup table. However, the inserts and updates allowed by referential integrity occur when the data inserted is located in the lookup table. In addition, updates and deletes in the lookup table prevented by referential integrity occur when the data in the foreign key column of the data table is not present in the lookup table.
Consequently, the inserts and deletes allowed by referential integrity come from data located in the lookup table. In addition to the updates and deletes authorized by referential integrity, there are three options associated with it:
Restrict: this is the default value if no other option is set
Set null: sets all matching in the foreign key column to null; all other values are unchanged
Cascade: composed of two parts
Deletes: an entire row is deleted from the data table when it matches a value in the foreign key column
Updates: values in the foreign key column are changed to the new value; all other values are unchanged
Data Validation is also a key in databases created through Microsoft Access. Data validation can be implemented during the design process of a database by setting data requirements for the user input to avoid errors. There are several different ways to validate data through Microsoft Access, some of which include:
1. Validation Rule Property: This property allows the database designer to set a validation rule, so that data inputted into the database must follow a certain rule. Example: Student titles such as Freshman, Sophomore, Junior, and Senior must be entered as ‘FR’, ‘SF’, ‘JR’, or ‘SR’. The database designer can also implement a validation rule text that displays a message stating the above rule if entered incorrectly.
2. Data Types: You can restrict data types that are entered into an Access database by setting a certain required data type. Example: If a data type is set to be ‘numeric’, then all other types, such as a character(s) will be denied with an error. By setting an input mask in a field in Microsoft Access, it controls the way data can be entered. Example: Input masks can specify that social security numbers be entered in the form of ‘AAA"-"AA"-"AAAA’. By using this setting the user’s input automatically formats to the specified form.
3. Required Property: Using the required property is an easy way to avoid null values in unwanted areas. If the required property is set for a certain field but the user attempts to leave it blank, they will be prompted with an error message, requiring data to be entered before going any further.
Patrick, John J. SQL Fundamentals: 3rd Edition. Boston: Prentice Hall, 2009.
"Validating Data in Microsoft Access | Database Solutions for Microsoft Access | databasedev.co.uk." Database Solutions & Downloads for Microsoft Access | databasedev.co.uk. Web. 05 Dec. 2009.