Search code examples
mysqlsqldatabaseconstraintssql-insert

Can't run NOCHECK CONSTRAINT on mysql - SQL syntax error

I've create a table called Students. It has 4 fields: Name, Age, Class, Years.

enter image description here

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(50) | NO   | PRI | NULL    |       |
| Age   | smallint    | YES  |     | 18      |       |
| Class | varchar(10) | YES  |     | NULL    |       |
| Years | smallint    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

I have added a CHECK CONSTRAINT on Years field that means that the value in Years column can't be lower than 3. I did it by

mysql> alter table Students
    -> add constraint CK_Years check (Years >= 3);

My goal here is to insert a query with a record which has a Years value of 2, which means it won't pass the CK_Years check constraint.

I want to do it without changing the constraint or the fields in the insert statement. Is it possible?

mysql> insert into Students
    -> (Name, Age, Class, Years) values ("Eric","25","110","2");

I have tried:

mysql> alter table Students
    -> NOCHECK CONSTRAINT CK_Years;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOCHECK CONSTRAINT CK_Years' at line 2

I want to disable it just for this insert, and then bring it back (the check constraint).

As you can see, I got this error. I tried to write it in couple ways. Wit 'CK_Years' or CK_Years or

`CK_Years`

got the same error for all of these ways.

I have verified that I have the constraint in the table:

mysql> select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    -> where table_name='Students';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | School          | PRIMARY         | School     | Students   | PRIMARY KEY     | YES      |
| def                | School          | CK_Years        | School     | Students   | CHECK           | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+

So how can I do it without changing the constraint or the fields in the insert statement? How can I fix the syntax error? And what is the reason for it?

Thanks.

Solution

  • MySQL does support syntax to alter a check constraint so it is not enforced, but it's different from the Microsoft SQL Server syntax.

    mysql> alter table students alter check CK_Years NOT ENFORCED;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into students values ('Eric',25,'110',2);
Query OK, 1 row affected (0.00 sec)

    It's documented here: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

    If you re-enable it, the constraint is enforced, and if you had inserted any rows that violate the constraint, you get an error trying to re-enable it.

    mysql> alter table students alter check CK_Years ENFORCED;
ERROR 3819 (HY000): Check constraint 'CK_Years' is violated.

    Also once you make the constraint not enforced, it would take effect for all clients, not just for one INSERT.

    So I don't think you can do what you intend with a CHECK constraint. You may have to write a trigger that enforces a similar condition on that column, but has logic to enforce it conditionally.

    Or do what most developers did before MySQL supported CHECK constraints: enforce it in the client app, before doing the INSERT. Then you can apply any conditions you want.