Altering and dropping 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 drop 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 Customers table:
ALTER TABLE Customers ALTER 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 Customers table:
ALTER TABLE Customers ALTER Phone CHECK ( Phone LIKE '___-___-____' );

You can alter 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 alter individual constraints.

If you have not defined constraint names, you can drop 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, alter and drop both table and column CHECK constraints. For more information, see Working with table and column constraints in Sybase Central.

Dropping a column from a table does not drop 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.

Note

Table CHECK constraints fail if a value of FALSE is returned. If the condition returns a value of UNKNOWN the behavior is as though it returned TRUE, and the value is allowed.

Send feedback about this page via email or DocCommentXchange