Adding new values to CHECK Constraints should not put Table in CHECK PENDING

    There are many instances today when existing CHECK Constraints have to be modified to add additional values. Currently, making any modifications to CHECK Constraints to add or remove values requires that the Constraint be dropped and re-created.  This causes an outage on the table as the table is put into CHECK PENDING until either a CHECK DATA or REPAIR Utility is run to remove the CHECK PENDING status.  If a table is very large, and unless a REPAIR Utility is run, the outage can be significant.

      This idea goes to higher availability for shops that require 24x7 processing and is intended to reduce the outages caused by certain changes to CHECK Constraints.

     Specifically:

1)      Adding a value to an existing CHECK Constraint should not require a DROP/CREATE of the existing constraint.  A new SQL command such as those suggested below can be use to simply add a value to an existing constraint.  The ALTER command would verify that the new value being added conforms to the requirements of the column’s data type. Since it is assumed that a given Table’s data already conforms to any existing constraint, adding a new value to that constraint should have no impact on the Table since the new value would have been restricted from the Table with the existing Constraint.   Additionally, new values can be added to any constraint in advance of any related application code moves.

a.      ALTER CONSTRAINT  or 

b.      ALTER TABLE tname  ALTER CONSTRAINT constname

2)      Dropping obsolete values from CHECK constraints should also not require a DROP/CREATE of the existing constraint.  However to ensure that a valid value is not being removed, a DROP of a constraint value might be handled in a couple of ways:

a.      Have the command detect if at least one occurrence of the value exists and fail the command if it does.

b.      Have the command execute as a PENDING change and place the Table in a new status of Advisory Check Pending.  If a subsequent CHECK DATA does not find any occurrences of the dropped value the Advisory Check Pending would be removed; otherwise, the Advisory status would remain.  The user can then decide to have any data updated separately or, since the change is a PENDING change, it can be dropped.

3)      To facilitate #2, the CHECK DATA Utility should be enhanced to allow for checking only CHECK Constraint values and possibly only specific Constraints.  A Table may have many Constraints defined and for large Tables, if only one Constraint is changed it would seem appropriate to be able to request that only that one Constraint be checked.  By allowing to focus in on only one CHECK Constraint the Elapsed Time and/or CPU Time may be reduced considerably.