Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

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.