Data integrity refers to the correctness and completeness of data within a database. To enforce data integrity, you can constrain or restrict the data values that users can insert, delete, or update in the database. For example, the integrity of data in the pubs2 and pubs3 databases requires that a book title in the titles table must have a publisher in the publishers table. You cannot insert books that do not have a valid publisher into titles, because it violates the data integrity of pubs2 or pubs3.
Transact-SQL provides several mechanisms for integrity enforcement in a database such as rules, defaults, indexes, and triggers. These mechanisms allow you to maintain these types of data integrity:
Requirement – requires that a table column must contain a valid value in every row; it cannot allow null values. The create table statement allows you to restrict null values for a column.
Check or validity – limits or restricts the data values inserted into a table column. You can use triggers or rules to enforce this type of integrity.
Uniqueness – no two table rows can have the same non-null values for one or more table columns. You can use indexes to enforce this integrity.
Referential – data inserted into a table column must already have matching data in another table column or another column in the same table. A single table can have up to 192 references.
Consistency of data values in the database is another example of data integrity, which is described in Chapter 23, “Transactions: Maintaining Data Consistency and Recovery.”
As an alternative to using rules, defaults, indexes, and triggers, Transact-SQL provides a series of integrity constraints as part of the create table statement to enforce data integrity as defined by the SQL standards. These integrity constraints are described later in this chapter.
