Defining integrity constraints for tables

Transact-SQL provides two methods of maintaining data integrity in a database:

• Defining rules, defaults, indexes, and triggers

• Defining create table integrity constraints

Choosing one method over the other depends on your requirements. Integrity constraints offer the advantages of defining integrity controls in one step during the table creation process (as defined by the SQL standards) and of simplifying the process to create those integrity controls. However, integrity constraints are more limited in scope and less comprehensive than defaults, rules, indexes, and triggers.

For example, triggers provide more complex handling of referential integrity than those declared in create table. The integrity constraints defined by a create table are specific to that table; you cannot bind them to other tables, and you can only drop or change them using alter table. Constraints cannot contain subqueries or aggregate functions, even on the same table.

The two methods are not mutually exclusive. You can use integrity constraints along with defaults, rules, indexes, and triggers. This gives you the flexibility to choose the best method for your application. This section describes the create table integrity constraints. Defaults, rules, indexes, and triggers are described in later chapters.

You can create these types of constraints:

• unique and primary key constraints require that no two rows in a table have the same values in the specified columns. In addition, a primary key constraint does not allow a null value in any row of the column.

• Referential integrity (references) constraints require that data being inserted in specific columns already has matching data in the specified table and columns. Use sp_helpconstraint to find a table’s referenced tables.

• check constraints limit the values of data inserted into columns.