Transact-SQL provides two methods for 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 the following 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 requires that there not be a null value in any row of the column.
Referential integrity (references) constraints require that data being inserted in specific columns already have 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.
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). See “Allowing null values in a column” for information about the null and not null keywords.
For information about any constraints defined for a table, see “Using sp_helpconstraint to find a table’s constraint information”.
WARNING! Do not define or alter the definitions of constraints for system tables.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
