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