You can declare integrity constraints at the table or column level. Although the difference is rarely noticed by users, column-level constraints are verified only if a value in the column is being modified, while the table-level constraints are verified whenever there is any modification to a row, regardless of whether or not it changes the column in question.
Place column-level constraints after the column name and datatype, but before the delimiting comma. 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 optionally declare constraints that operate on a single column as column-level constraints. 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))
On either column-level or table-level constraints, the constraint keyword and accompanying constraint_name are optional. The check constraint is described in “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.
