Alter and drop CHECK constraints

Altering tables can interfere with other users of the database. Although you can execute the ALTER TABLE statement while other connections are active, you cannot execute the ALTER TABLE statement if any other connection is using the table you want to alter. For large tables, ALTER TABLE is a time-consuming operation, and all other requests referencing the table being altered are prohibited while the statement is processing.

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.

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 an 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.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1