Define Integrity Constraints for Tables

To maintain data integrity in a database, you can either define rules, defaults, indexes, and triggers; or, you can define create table integrity constraints.

The method select 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 also enforce data integrity by restricting the use of null values in a column (the null or not null keywords) and by providing default values for columns (the default clause).

• Table and Column Level Constraints
  You can declare integrity constraints at the table or column level.
• Create Error Messages for Constraints
  You can use sp_addmessage to create error messages, then use sp_bindmsg to bind them to constraints.
• Check Constraints
  You can declare a check constraint to limit the values users insert into a column in a table.
• Default Column Values
  Before you define any column-level integrity constraints, you can use the default clause to assign a default value to a column as part of the create table statement. When you do not enter a value for a column, the default value is inserted.
• unique and primary key Constraints
  You can declare unique or primary key constraints to ensure that no two rows in a table have the same values in the specified columns.
• Referential Integrity Constraints
  Referential integrity refers to the methods used to manage the relationships between tables. When you create a table, you can define constraints to ensure that the data inserted into a particular column has matching values in another table.