Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 297 / 536

CHAPTER 7 Ensuring Data Integrity
277
Using table and column constraints
The CREATE TABLE statement and ALTER TABLE statement can specify
many different attributes for a table. Along with the basic table structure
(number, name and data type of columns, name and location of the table), you
can specify other features that allow control over data integrity.
Warning! Altering tables can interfere with other users of the database.
Although the ALTER TABLE statement can be executed while other
connections are active, it is prevented if any other connection is using the table
to be altered. For large tables, ALTER TABLE can be a time-consuming
operation, and no other requests referencing the table being altered are allowed
while the statement is being processed.
This section describes how to use constraints to help ensure that the data
entered in the table is correct, and to provide information to Adaptive Server
IQ that boosts performance.
Using UNIQUE constraints on columns or tables
The
UNIQUE
constraint specifies that one or more columns uniquely identify
each row in the table. If you apply the
UNIQUE
constraint to a single column,
Adaptive Server IQ enforces this condition. If multiple columns are required to
uniquely identify a row, you must specify
UNIQUE
as an unenforced table
constraint.
UNIQUE
is essentially the same as a
PRIMARY KEY
constraint, except that you
can specify more than one
UNIQUE
constraint in a table. With both
UNIQUE
and
PRIMARY KEY
, a column must not contain any
NULL
values.
Example 1 The following example adds the column
ss_number
to the
employee
table, and
ensures that each value in it is unique throughout the table.
ALTER TABLE employee
ADD ss_number char(11) UNIQUE
Example 2 In this example, three columns are needed to make a unique entry. Therefore,
the
UNIQUE
constraint is unenforced.
ALTER TABLE product
ADD UNIQUE (name, size, color) UNENFORCED