beautypg.com

Using iq unique constraint on columns, Using check conditions on columns – Sybase 12.4.2 User Manual

Page 298

background image

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