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

asked 11 Jun '12, 10:23

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 19 Jun '12, 03:24


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

permanent link

answered 11 Jun '12, 10:27

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 11 Jun '12, 10:28

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 ALTER TABLE ADD [WITH { CHECK | NOCHECK } ] CONSTRAINT ... CHECK syntax to be considered in a future SQL Anywhere release. Thank you for the product suggestion!

permanent link

answered 18 Jun '12, 15:07

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×406
×106
×11
×8
×2

question asked: 11 Jun '12, 10:23

question was seen: 1,401 times

last updated: 19 Jun '12, 03:24