Does adding a CHECK CONSTRAINT apply only to future data modifications?

[VolkerBarth]

Testing with v12.0.1.3726 on Windows, I'm somewhat surprised that an ALTER TABLE ADD CONSTRAINT CHECK (whatsoever) does not seem to fail when the underlying data won't satisfy the check.

I would have bet it is the other way, i.e. it is not possible to add a table check constraint when the existing data violate the check condition. - But I seem to be wrong...

Does that mean adding a CHECK CONSTRAINT (here a table) will not check the existing table data, whereas adding a PRIMARY/UNIQUE KEY or an FOREIGN KEY surely does check the entity/referential integrity (and will fail accordingly) - so the check will only apply to future DML statements?

(Note: The according table check does work for future modifications, so it's not the particular check condition that might be wrong...)

[VolkerBarth · Accepted solution]

Ah, the typical steps: Once I post a question, I stumble over something "hidden" in the docs...

Once a CHECK condition is in place, future values are evaluated against the condition before a row is modified. When you update a value that has a check constraint, the constraints for that value and for the rest of the row are checked.

So the future tests are documented, however, it still does not really tell whether a check of the existing data base is done before "a CHECK condition is in place"...

[Former Member · Accepted solution]

You are correct, Volker - existing rows in a table are not verified that they satisfy a new or modified CHECK constraint.

[VolkerBarth · Accepted solution]

And now I bet it has been that way all these years...

FWIW, I guess I have mapped the semantics of the MS SQL Server "ALTER TABLE ADD [WITH { CHECK | NOCHECK } ] CONSTRAINT ... CHECK" syntax to SQL Anywhere - as MS SQL does check new constraints by default...

One of the rare occasions where SQL Anywhere does not behave as expected (by me, at least)...

Are there any plans to consider such a WITH CHECK feature, Glenn?

[Former Member · Accepted solution]

No plans at this time, Volker.

[Breck_Carter · Accepted solution]

Not checking the new constraint at ALTER time does seem to violate the rule "Watcom does things the way they should be done".

[VolkerBarth · Accepted solution]

Yep, that's simply why I was surprised. - On the other hand, I haven't apparently run into issues with the current behaviour for years...

[jeff_albion]

Hi Volker,

I have opened CR #711831 for this enhancement suggestion for the ALTER TABLE ADD [WITH { CHECK | NOCHECK } ] CONSTRAINT ... CHECK syntax to be considered in a future SQL Anywhere release. Thank you for the product suggestion!