Question Re: Does adding a CHECK CONSTRAINT apply only to future data modifications? in Technology Q&A

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

VolkerBarth — Mon, 11 Jun 2012 10:23:20 GMT

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

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

VolkerBarth — Mon, 11 Jun 2012 10:27:18 GMT

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

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

Former Member — Mon, 11 Jun 2012 10:47:29 GMT

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

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

VolkerBarth — Mon, 11 Jun 2012 11:09:39 GMT

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?

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

Former Member — Mon, 11 Jun 2012 11:33:31 GMT

No plans at this time, Volker.

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

Breck_Carter — Mon, 11 Jun 2012 12:11:15 GMT

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

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

VolkerBarth — Mon, 11 Jun 2012 15:34:20 GMT

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

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

jeff_albion — Mon, 18 Jun 2012 15:07:35 GMT

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!