|
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...) |
|
Ah, the typical steps: Once I post a question, I stumble over something "hidden" in the docs...
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"... You are correct, Volker - existing rows in a table are not verified that they satisfy a new or modified CHECK constraint.
(11 Jun '12, 10:47)
Glenn Paulley
Replies hidden
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?
(11 Jun '12, 11:09)
Volker Barth
No plans at this time, Volker.
(11 Jun '12, 11:33)
Glenn Paulley
3
Not checking the new constraint at ALTER time does seem to violate the rule "Watcom does things the way they should be done".
(11 Jun '12, 12:11)
Breck Carter
Replies hidden
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...
(11 Jun '12, 15:34)
Volker Barth
|
|
Hi Volker, I have opened CR #711831 for this enhancement suggestion for the |
