Using table and column constraints

Chapter 18. 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.

Caution
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 is 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.

Using CHECK conditions on columns

A CHECK condition can be applied to values in a single column, to ensure that they satisfy rules. 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.

You use a CHECK condition to ensure that the values in a column satisfy some definite criterion.

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 enforce a particular formatting requirement. If a table has a column for phone numbers you may wish to ensure that they are all 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 '(___) ___-____' )

Example 2

You can ensure that the entry matches one of a limited number of values. For example, to ensure 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' ) )

By default, string comparisons are case insensitive unless the database is explicitly created as a case-sensitive database.

Example 3

You can ensure that a date or number falls in a particular range. For example, you may want to require that the start_date column of an employee table must be between the date the organization was formed and the current date. This could be achieved as follows:

     ALTER TABLE employee
     MODIFY start_date
     CHECK ( start_date BETWEEN '1983/06/27'
     AND CURRENT DATE )

SQL Anywhere supports several date formats: the YYYY/MM/DD format used in this example has the virtue of always being recognized regardless of the current option settings.

Column CHECK tests only fail if the condition returns a value of FALSE. If a value of UNKNOWN is returned, the change is allowed.

Column CHECK conditions in previous releases
There is a change in the way that column CHECK conditions are held in this release. In previous releases, column CHECK conditions were merged together with all other CHECK conditions on a table into a single CHECK condition. Consequently, they could not be individually replaced or deleted. In this release, column CHECK conditions are held individually in the system tables, and can be replaced or deleted individually. Column CHECK conditions added before this release are still held in a single table constraint, even if the database is upgraded.

Column CHECK conditions from user-defined data types

You can attach CHECK conditions to user-defined data types, and columns defined on those data types inherit the CHECK conditions. A CHECK condition explicitly specified for the column overrides that from the user-defined data type.

When defining a CHECK condition on a user-defined data type, any variable prefixed with the @ sign is replaced by the name of the column when the CHECK condition is evaluated. For example, the following user-defined data type accepts only positive integers:

     CREATE DATATYPE posint INT
     CHECK ( @col > 0 )

Any variable name prefixed with @ could be used instead of @col. Any column defined using the posint data type accepts only positive integers unless it has a CHECK condition explicitly specified.

An ALTER TABLE statement with the DELETE CHECK clause deletes all CHECK conditions from the table definition, including those inherited from user-defined data types.

For information on user-defined data types, see "User-defined data types".

Working with column constraints in Sybase Central

All adding, altering, and deleting of column constraints in Sybase Central is carried out in the Constraints tab of the column properties sheet.

To display the property sheet for a column:

Connect to the database.
Click the Tables folder for that database, and click the table holding the column you wish to change.
Double-click the Columns folder to open it, and double-click the column to display its property sheet.

For more information, see the Sybase Central online Help.

Using CHECK conditions on tables

A CHECK condition can be applied as a constraint on the table, instead of on a single column. Such CHECK conditions typically ensure that two values in a row being entered or modified have a proper relation to each other. Column CHECK conditions are held individually in the system tables, and can be replaced or deleted individually. This is more flexible behavior, and CHECK conditions on individual columns are recommended where possible.

For example, in a library database, the date_returned column for a particular entry must be later than (or the same as) the date_borrowed entry:

     ALTER TABLE loan
     ADD CHECK(date_returned >= date_borrowed)

Modifying and deleting CHECK conditions

There are several ways of altering the existing set of CHECK conditions on a table.

You can add a new CHECK condition to the table or to an individual column, as described above.
You can delete a CHECK condition on a column by setting it to NULL. The following statement removes the CHECK condition on the phone column in the customer table: ALTER TABLE customer MODIFY phone CHECK NULL
You can replace a CHECK condition on a column in the same was as adding a CHECK condition. The following statement adds or replaces a CHECK condition on the phone column of the customer table:
```
     ALTER TABLE customer
     MODIFY phone
     CHECK ( phone LIKE '___-___-____' )
```
There are two ways of modifying a CHECK condition defined on the table, as opposed to a CHECK condition defined on a column.
You can add a new CHECK condition using ALTER TABLE with an ADD table-constraint clause.
You can delete all existing CHECK conditions, including column CHECK conditions, using ALTER TABLE DELETE CHECK, and then add in new CHECK conditions.

All CHECK conditions on a table, including CHECK conditions on all its columns and CHECK conditions inherited from user-defined data types, are removed using the ALTER TABLE statement with the DELETE CHECK clause, as follows:

     ALTER TABLE table_name
     DELETE CHECK

Deleting a column from a table does not delete CHECK conditions associated with the column that are held in the table constraint. If the constraints are not removed, any attempt to query data in the table will produce

a column not found

error message.

Table CHECK conditions fail only if a value of FALSE is returned. If a value of UNKNOWN is returned, the change is allowed.