I added a simple check constraint to a table that contained some rows violating the constraint; I was expecting to be blocked by the engine due to an integrity check of old data but the schema was saved without warning. The constraint works as expected on new insertions or updates. Is it the expected behavior?
I'm using: XAMPP 3.3 (Windows 10), MariaDB 10.4.21, and SQLYog community edition as frontend
CodePudding user response：
Yes this is expected behaviour as a CHECK constraint will
Before a row is inserted or updated, all constraints are evaluated in the order they are defined.
As the old data are already inserted you will not get a an error message of a violation.
You can run a SELECT to check for all violations and update them accourding to a plan, or or could rename the old table create a new one with the old name and insert all data, this will stop when you hit a constraint, but the forst way is more secure