There are several ways to alter 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 way as you would add a CHECK condition. The following statement adds or replaces a CHECK condition on the city column of the office table:
ALTER TABLE office MODIFY city CHECK ( city IN ( 'city_1', 'city_2', 'city_3' ) )
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 produces a column not found error
message.
