Data Integrity Basics
The first step in specifying the domains of a table is to determine column data types. A domain is the set of all allowed values in a column. A domain includes not only the concept of enforcing data types, but also the values allowed in the column. For example, the domain for the Color column of the Production.Product table includes the data type nvarchar, and a size limit of 15 characters. The domain could also specify the character strings allowed in the column, such as Red, Blue, Green, Yellow, Brown, Black, White, Teal, Gray, and Silver. For more information, see Data Types (Database Engine).
Null Values
Columns can either accept or reject null values. NULL is a special value in databases that represents the concept of an unknown value. NULL is different from a blank character or 0. Blank is actually a valid character, and 0 is a valid number. NULL just represents the idea that we do not know what this value is. NULL is also different from a zero-length string. If a column definition contains the NOT NULL clause, you cannot insert rows that have the value NULL for that row. If the column definition has only the NULL keyword, it accepts null values.
Allowing null values in a column can increase the complexity of any logical comparisons that use the column. The SQL-92 standard states that any comparison against NULL does not evaluate to TRUE or FALSE, it evaluates to UNKNOWN. This introduces three-value logic to comparison operators, which can be difficult to manage correctly.
Constraints, Rules, Defaults, and Triggers
Table columns have properties besides data type and size. These other properties are an important part of guaranteeing the integrity of the data and the referential integrity of the tables in a database:
- Data integrity refers to each occurrence of a column having a correct data value. The data values must be of the right data type and in the correct domain.
- Referential integrity indicates that the relationships between tables have been correctly maintained. Data in one table should only point to existing rows in another table and not point to rows that do not exist.
The following objects are used to maintain both types of integrity: