Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search
Managing identity gaps in tables [Table of Contents] How to design and create a table

Transact-SQL User's Guide

[-] Chapter 7 Creating Databases and Tables
[-] Defining integrity constraints for tables

Defining integrity constraints for tables

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

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. Also, the integrity constraints defined by a create table are specific for 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:

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" 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.

Specifying table-level or column-level constraints

You can declare integrity constraints at the table or column level. Although the difference is rarely noticed by users, column-level constraints are only checked if a value in the column is being modified, while the table-level constraints are checked if there is any modification to a row, regardless of whether or not it changes the column in question.

You place column-level constraints after the column name and datatype, but before the delimiting comma. You enter table-level constraints as separate comma-delimited clauses. Adaptive Server treats table-level and column-level constraints the same way; both ways are equally efficient

However, you must declare constraints that operate on more than one column as table-level constraints. For example, the following create table statement has a check constraint that operates on two columns, pub_id and pub_name:

create table my_publishers
(pub_id      char(4),
pub_name     varchar(40),
constraint my_chk_constraint 
    check (pub_id in ("1389", "0736", "0877")
        or pub_name not like "Bad News Books"))

You can declare constraints that operate on just one column as column-level constraints, but it is not required. For example, if the above check constraint uses only one column (pub_id), you can place the constraint on that column:

create table my_publishers
(pub_id     char(4) constraint my_chk_constraint 
        check (pub_id in ("1389", "0736", "0877")),
pub_name     varchar(40))

In either case, the constraint keyword and accompanying constraint_name are optional. The check constraint is described under "Specifying check constraints".

You cannot issue create table with a check constraint and then insert data into the table in the same batch or procedure. Either separate the create and insert statements into two different batches or procedures, or use execute to perform the actions separately.

Creating error messages for constraints

You can create error messages and bind them to constraints by creating messages with sp_addmessage and binding them to constraints with sp_bindmsg.

For example:

sp_addmessage 25001, 
     "The publisher ID must be 1389, 0736, or 0877"
sp_bindmsg my_chk_constraint, 25001
insert my_publishers values 
     ("0000", "Reject This Publisher")
Msg 25001, Level 16, State 1:
Server 'snipe', Line 1:
The publisher ID must be 1389, 0736, or 0877
Command has been aborted.

To change the message for a constraint, bind a new message. The new message replaces the old message.

Unbind messages from constraints using sp_unbindmsg; drop user-defined messages using sp_dropmessage.

For example:

sp_unbindmsg my_chk_constraint
sp_dropmessage 25001

To change the text of a message but keep the same error number, unbind it, drop it with sp_dropmessage, add it again with sp_addmessage, and bind it with sp_bindmsg.

After creating a check constraint

After you create a check constraint, the source text describing the check constraint is stored in the text column of the syscomments system table. Do not remove this information from syscomments; doing so can cause problems for future upgrades of Adaptive Server. If you have security concerns, encrypt the text in syscomments by using sp_hidetext, described in the Reference Manual. For more information, see "Compiled objects".

Specifying default column values

Before you define any column-level integrity constraints, you can specify a default value for the column with the default clause. The default clause assigns a default value to a column as part of the create table statement. When a user does not enter a value for the column, Adaptive Server inserts the default value.

You can use the following values with the default clause:

For example, this create table statement defines two column defaults:

create table my_titles
(title_id        char(6),
title            varchar(80),
price            money       default null,
total_sales      int         default 0)

You can include only one default clause per column in a table.

Using the default clause to assign defaults is simpler than the two-step Transact-SQL method. In Transact-SQL, you can use create default to declare the default value and then bind it to the column with sp_bindefault.

Specifying 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. Both constraints create unique indexes to enforce this data integrity. However, primary key constraints are more restrictive than unique constraints. Columns with primary key constraints cannot contain a NULL value. You normally use a table's primary key constraint in conjunction with referential integrity constraints defined on other tables.

The definition of unique constraints in the SQL standards specifies that the column definition shall not allow null values. By default, Adaptive Server defines the column as not allowing null values (if you have not changed this using sp_dboption) when you omit null or not null keywords in the column definition. In Transact-SQL, you can define the column to allow null values along with the unique constraint, since the unique index used to enforce the constraint allows you to insert a null value.

Do not confuse the unique and primary key integrity constraints with the information defined by sp_primarykey, sp_foreignkey, and sp_commonkey. The unique and primary key constraints actually create indexes to define unique or primary key attributes of table columns. sp_primarykey, sp_foreignkey, and sp_commonkey define the logical relationship of keys (in the syskeys table) for table columns, which you enforce by creating indexes and triggers.

unique constraints create unique nonclustered indexes by default; primary key constraints create unique clustered indexes by default. You can declare either clustered or nonclustered indexes with either type of constraint.

For example, the following create table statement uses a table-level unique constraint to ensure that no two rows have the same values in the stor_id and ord_num columns:

create table my_sales
(stor_id     char(4),
ord_num      varchar(20),
date         datetime,
unique clustered (stor_id, ord_num))

There can be only one clustered index on a table, so you can specify only one unique clustered or primary key clustered constraint.

You can use the unique and primary key constraints to create unique indexes (including the with fillfactor, with max_rows_per_page, and on segment_name options) when enforcing data integrity. However, indexes provide additional capabilities. For information about indexes and their options, including the differences between clustered and nonclustered indexes, see Chapter 11, "Creating Indexes on Tables."

Specifying 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.

There are three types of references you can define in a table: references to another table, references from another table, and self-references, that is, references within the same table.

The following two tables from the pubs3 database illustrate how declarative referential integrity works. The first table, stores, is a "referenced" table:

create table stores
(stor_id     char(4) not null,
stor_name    varchar(40) null,
stor_address varchar(40) null,
city         varchar(20) null,
state        char(2) null,
country      varchar(12) null,
postalcode   char(10) null,
payterms     varchar(12) null,
unique nonclustered (stor_id))

The second table, store_employees, is a "referencing table" because it contains a reference to the stores table. It also contains a self-reference:

create table store_employees
(stor_id     char(4) null
       references stores(stor_id),
emp_id       id not null,
mgr_id       id null
       references store_employees(emp_id),
emp_lname    varchar(40) not null,
emp_fname    varchar(20) not null,
phone        char(12) null,
address      varchar(40) null,
city         varchar(20) null,
state        char(2) null,
country      varchar(12) null,
postalcode   varchar(10) null,
unique nonclustered (emp_id))

The references defined in the store_employees table enforce the following restrictions:

Table-level or column-level referential integrity constraints

You can define referential integrity constraints at the column level or the table level. The referential integrity constraints in the preceding examples were defined at the column level, using the references keyword in the create table statement.

When you define table-level referential integrity constraints, include the foreign key clause and a list of one or more column names. foreign key specifies that the listed columns in the current table are foreign keys whose target keys are the columns listed the following references clause. For example: 

constraint sales_detail_constr
     foreign key (stor_id, ord_num)
     references my_salesdetail(stor_id, ord_num)

The foreign key syntax is permitted only for table-level constraints, and not for column-level constraints. For more information, see "Specifying table-level or column-level constraints".

After defining referential integrity constraints at the column level or the table level, you can use sp_primarykey, sp_foreignkey, and sp_commonkey to define the keys in the syskeys system table.

Maximum number of references allowed for a table

The maximum number of references allowed for a table is 192. You can check a table's references by using sp_helpconstraint, described under "Using sp_helpconstraint to find a table's constraint information".

Using create schema for cross-referencing constraints

You cannot create a table that references a table that does not yet exist. To create two or more tables that reference each other, use create schema.

A schema is a collection of objects owned by a particular user, and the permissions associated with those objects. If any of the statements within a create schema statement fail, the entire command is rolled back as a unit, and none of the commands take effect.

The create schema syntax is: 

create schema authorization authorization_name
create_oject_statement 
    [ create_object_statement ... ]
[ permission_statement ... ]

For example:

create schema authorization dbo
    create table list1
        (col_a char(10) primary key,
          col_b char(10) null
        references list2(col_A))
    create table list2
        (col_A char(10) primary key,
          col_B char(10) null
        references list1(col_a))

General rules for creating referential integrity constraints

When you define referential integrity constraints in a table:

Referential integrity constraints provide a simpler way to enforce data integrity than triggers. However, triggers provide additional capabilities to enforce referential integrity between tables. For more information, see Chapter 16, "Triggers: Enforcing Referential Integrity."

Specifying check constraints

You can declare a check constraint to limit the values users insert into a column in a table. Check constraints are useful for applications that check a limited, specific range of values. A check constraint specifies a search_condition that any value must pass before it is inserted into the table. A search_condition can include:

An expression can include arithmetic operations and Transact-SQL built-in functions. The search_condition cannot contain subqueries, a set function specification, or a target specification.

For example, this statement ensures that only certain values can be entered for the pub_id column:

create table my_new_publishers
(pub_id      char(4)
        check (pub_id in ("1389", "0736", "0877",
             "1622", "1756")
        or pub_id like "99[0-9][0-9]"),
pub_name     varchar(40),
city         varchar(20),
state        char(2))

Column-level check constraints can reference only the column on which the constraint is defined; they cannot reference other columns in the table. Table-level check constraints can reference any columns in the table. create table allows multiple check constraints in a column definition.

Because check constraints do not override column definitions, you cannot use a check constraint to prohibit null values if the column definition permits them. If you declare a check constraint on a column that allows null values, you can insert NULL into the column, implicitly or explicitly, even though NULL is not included in the search_condition. For example, suppose you define the following check constraint on a table column that allows null values: 

check (pub_id in ("1389", "0736", "0877", "1622", "1756"))

You can still insert NULL into that column. The column definition overrides the check constraint because the following expression always evaluates to true: 

col_name != null

Designing applications that use referential integrity

When you design applications that use referential integrity features:

Managing identity gaps in tables [Table of Contents] How to design and create a table