Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 298 / 536

Using table and column constraints
278
Using IQ UNIQUE constraint on columns
The
IQ UNIQUE
constraint specifies an estimate of the number of distinct
values in a column. You can apply the
IQ UNIQUE
constraint to any column in
a table. This constraint helps optimize loading of indexes.
For example, in the
state
column of the
employee
table, you would specify
IQ
UNIQUE(50)
to indicate that there are only 50 possible values (assuming U.S.
states only). Each of the possible values can occur many times.
Using CHECK conditions on columns
You can use a CHECK condition to specify that the values in a column must
satisfy some definite criterion.
You can apply an unenforced CHECK condition to values in a single column,
to specify the rules they should follow. These rules may be rules that data must
satisfy in order to be reasonable, or they may be more rigid rules that reflect
organization policies and procedures.
CHECK conditions on individual column values are useful when only a
restricted range of values are valid for that column. Here are some examples:
Example 1 You can specify a particular formatting requirement. If a table has a column for
phone numbers you can specify that they all be entered in the same manner. For
North American phone numbers, you could use a constraint such as the
following:
ALTER TABLE customer
MODIFY phone
CHECK ( phone LIKE ’(___) ___-____’ ) UNENFORCED
Note
The keyword
UNENFORCED
must appear after every
CHECK
condition.
Example 2
You can specify that the entry should match one of a limited number of values.
For example, to specify that a
city
column only contains one of a certain number
of allowed cities (say, those cities where the organization has offices), you
could use a constraint like the following:
ALTER TABLE office
MODIFY city
CHECK ( city IN ( ’city_1’, ’city_2’, ’city_3’ ) )
UNENFORCED