Filter
Top Products
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