Sybase 12.4.2 Using table and column constraints, Using Unique constraints on columns or tables

CHAPTER 7 Ensuring Data Integrity

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

Example 1

Example 2

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.

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

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

277