Data Integrity

Data Integrity
By Roger Stuart

Enforcing data integrity ensures the quality of data in a database. For example, if a product is entered with a Product_ID value of 25 in a table named Products, the database should not allow another product to have an ID with the same value. Furthermore, if there is a column named Product_Rating that is intended to have values ranging from 1 to 10, the database should not accept a value below 1 and above 10 for this column. This can be accomplished by using the methods supported by SQL Server to enforce the integrity of the data.

SQL Server supports a number of methods that can be used to enforce data integrity. These methods include defining datatypes, NOT NULL definitions, DEFAULT definitions, IDENTITY properties, rules, constraints, triggers, and indexes.

Datatypes

A datatype is an attribute that specifies the type of data (e.g., character, integer, binary, etc.) that can be stored in a column, parameter, or variable. SQL Server provides a set of system-supplied datatypes. However, users can also create user-defined datatypes based on the system-supplied datatypes. System-supplied datatypes define all of the types of data that can be used with SQL Server. Datatypes can be used to enforce data integrity because the data entered or modified must conform to the type specified for the object. For example, a name cannot be stored in a column defined with the datetime datatype, as a datetime column can accept only date values.

NOT NULL Definitions

The nullability of a table column determines whether the rows in the table can contain a null value for that column. A null value in a column does not mean that the column has zero, blank, or a zero-length character string such as " ". Null in a column means that no data has been entered in that column. The presence of a null in a column implies that the value is either unknown or undefined.

The nullability of a column is defined while defining the column or while creating or modifying a table. The NULL keyword is used to specify that the column will allow null values. The NOT NULL keyword specifies that null values will not be allowed in the column.

DEFAULT Definitions

Each column in a row must contain a value even if that value is null. However, certain situations exist when a row is inserted in a table, but the value for a column is not known or the value does not yet exist. If the column allows null values, a row with a null value for that column can be inserted in the table. In some cases, nullable columns might not be desirable. In these cases, a DEFAULT definition can be defined for the column. Defaults specify what values are automatically inserted in a column if a value is not specified for the column when inserting a row in the table. For example, it is common to specify zero as the default for numeric columns and N/A as the default for string columns.