ASA SQL User's Guide
  Ensuring Data Integrity
    Using table and column constraints

Modifying and deleting CHECK constraints

There are several ways to alter the existing set of CHECK constraints on a table.

• You can add a new CHECK constraint to the table or to an individual column.

• You can delete a CHECK constraint on a column by setting it to NULL. For example, the following statement removes the CHECK constraint on the phone column in the customer table:

  ALTER TABLE customer
  MODIFY phone CHECK NULL

• You can replace a CHECK constraint on a column in the same way as you would add a CHECK constraint. For example, the following statement adds or replaces a CHECK constraint on the phone column of the customer table:

  ALTER TABLE customer
  MODIFY phone
  CHECK ( phone LIKE '___-___-____' )

• You can modify a CHECK constraint defined on the table:

  • You can add a new CHECK constraint using ALTER TABLE with an ADD table-constraint clause.

  • If you have defined constraint names, you can modify individual constraints.

  • If you have not defined constraint names, you can delete all existing CHECK constraints (including column CHECK constraints and CHECK constraints inherited from domains) using ALTER TABLE DELETE CHECK, and then add in new CHECK constraints.

    To use the ALTER TABLE statement with the DELETE CHECK clause:

    ALTER TABLE table_name
    DELETE CHECK

Sybase Central lets you add, modify and delete both table and column CHECK constraints. For more information, see Working with table and column constraints in Sybase Central.

Deleting a column from a table does not delete CHECK constraints associated with the column held in the table constraint. Not removing the constraints produces a column not found error message upon any attempt to insert, or even just query, data in the table.

Table CHECK constraints fail only if a value of FALSE is returned. A value of UNKNOWN allows the change.