CHECK constraint in MySQL is not working

Question

First I created a table like

CREATE TABLE Customer (
  SD integer CHECK (SD > 0),
  Last_Name varchar (30),
  First_Name varchar(30)
);

and then inserted values in that table

INSERT INTO Customer values ('-2','abc','zz');

MySQL doesn't show an error, it accepted the values.

Partially agree. Given that you tried to use it, it can be assumed that you were asking both questions. In fact, the answer you have accepted is mainly explaining why it does not work. — igorrs, Jan 10 '13 at 00:21
You can vote on this feature request: http://bugs.mysql.com/bug.php?id=3464 but it hasn't received any attention in a decade. — Jared Beck, Mar 12 '15 at 16:35
You can use CHECK constraints in MariaDB from [version 10.2.1](https://mariadb.com/kb/en/mariadb/mariadb-1021-release-notes/). — joanq, Oct 09 '16 at 10:41
Now that MySQL 8.0.16 has finally addressed this one it would be time to recognise silently (!) ignored inline REFERENCES specifications too (the other big SQL standard incompatibility): https://bugs.mysql.com/bug.php?id=102904. — Matthias Dieter Wallnöfer, May 26 '21 at 18:17

score 158 · Answer 1 · edited Dec 24 '22 at 20:46

158

MySQL 8.0.16 is the first version that supports CHECK constraints.

Read https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

If you use MySQL 8.0.15 or earlier, the MySQL Reference Manual says:

The CHECK clause is parsed but ignored by all storage engines.

Try a trigger...

mysql> delimiter //
mysql> CREATE TRIGGER trig_sd_check BEFORE INSERT ON Customer 
    -> FOR EACH ROW 
    -> BEGIN 
    -> IF NEW.SD<0 THEN 
    -> SET NEW.SD=0; 
    -> END IF; 
    -> END
    -> //
mysql> delimiter ;

edited Dec 24 '22 at 20:46

starball

20,030
7
43
238

answered Jan 22 '10 at 07:11

David Kerins

1,589
1
8
3

9

Here you will find how to trigger an error instead: http://stackoverflow.com/a/7189396/1144966 – petermeissner Aug 28 '13 at 09:56
45

This is among the vast sparkly rainbow of reasons that I will always use PostgreSQL instead of MySQL given a choice whatsoever. – Reinderien Nov 11 '14 at 07:05
6

I wonder if it would be 10 minute or 15 minute development in MySQL to throw a warning if the parser encounters a `CHECK` constraint defined. Ahhh, that would be too straightforward... – gaborsch Dec 14 '16 at 13:58

score 76 · Answer 2 · edited Jun 20 '20 at 09:12

Unfortunately MySQL does not support SQL check constraints. You can define them in your DDL query for compatibility reasons but they are just ignored.

There is a simple alternative

You can create BEFORE INSERT and BEFORE UPDATE triggers which either cause an error or set the field to its default value when the requirements of the data are not met.

Example for BEFORE INSERT working after MySQL 5.5

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
    IF CHAR_LENGTH( NEW.ID ) < 4 THEN
        SIGNAL SQLSTATE '12345'
            SET MESSAGE_TEXT := 'check constraint on Test.ID failed';
    END IF;
END$$   
DELIMITER ;

Prior to MySQL 5.5 you had to cause an error, e.g. call a undefined procedure.

In both cases this causes an implicit transaction rollback. MySQL does not allow the ROLLBACK statement itself within procedures and triggers.

If you don't want to rollback the transaction ( INSERT / UPDATE should pass even with a failed "check constraint" you can overwrite the value using SET NEW.ID = NULL which will set the id to the fields default value, doesn't really make sense for an id tho

Edit: Removed the stray quote.

Concerning the := operator:

Unlike =, the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.

https://dev.mysql.com/doc/refman/5.6/en/assignment-operators.html

Concerning backtick identifier quotes:

The identifier quote character is the backtick (“`”)

If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks

http://dev.mysql.com/doc/refman/5.6/en/identifiers.html

...not very simple, at least compared to CHECK :(. Coupla tutes: http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/, http://www.sitepoint.com/how-to-create-mysql-triggers/ — Ben, May 24 '13 at 06:15
ugh this looks very bulky. I think I rather create a tuple in python and check values there instead of putting this in. — OzzyTheGiant, Jan 05 '17 at 15:32
Quick question: why this doesn't work without setting the `DELIMITER`? — ddz, May 04 '17 at 02:03
But why? I mean, they could have just removed the command. What was the point of keeping the command and removing its functionality? — Jdeep, Apr 07 '21 at 08:24
The command is defined in the ANSI Standard for SQL which Mysql is following. https://dev.mysql.com/doc/refman/8.0/en/compatibility.html — Michel Feldheim, Apr 08 '21 at 09:24

score 53 · Answer 3 · answered Jan 09 '13 at 22:31

53

CHECK constraints are ignored by MySQL as explained in a miniscule comment in the docs: CREATE TABLE

The CHECK clause is parsed but ignored by all storage engines.

answered Jan 09 '13 at 22:31

ypercubeᵀᴹ

113,259
19
174
235

2

@thefiloe: Correct, in other DBMS with correct implementation of `CHECK` constraints, if the `CHECK` evaluates to `FALSE` then the insert (or update) is not done and an error is caused. – ypercubeᵀᴹ Jan 09 '13 at 22:43
Fixed in MariaDB (see this answer https://stackoverflow.com/a/44333349). – Jérôme Feb 02 '18 at 10:47
@Jérôme I know, I have some (more recent) answers that include improvements in this area (there had been other ways to workaround this issue, both in MariaDB and MySQL, before MariaDB properly implemented CHECK constraints). What I'm not sure is if I should go and edit all my old answers! – ypercubeᵀᴹ Feb 02 '18 at 12:02
I suppose my comment with a link to a more recent answer is fine. Or better than nothing. Perhaps I should have edited. I didn't mean to put pressure on you to do anything. – Jérôme Feb 02 '18 at 13:11

score 16 · Answer 4 · edited May 01 '15 at 21:18

16

The CHECK constraint doesn't seem to be implemented in MySQL.

See this bug report: https://bugs.mysql.com/bug.php?id=3464

edited May 01 '15 at 21:18

Mark Chesney

1,082
12
20

answered Jan 22 '10 at 06:37

ryanprayogo

11,587
11
51
66

1

Fixed in MariaDB (see this answer https://stackoverflow.com/a/44333349). – Jérôme Feb 02 '18 at 10:46

score 9 · Answer 5 · answered Jun 02 '17 at 16:17

9

As mentioned by joanq MariaDB now seems to support CHECK constraints among other goodies:

"Support for CHECK CONSTRAINT (MDEV-7563)."

https://mariadb.com/kb/en/mariadb/mariadb-1021-release-notes/

answered Jun 02 '17 at 16:17

Markus Barthlen

389
4
15

score 1 · Answer 6 · answered Jan 30 '19 at 11:16

Check constraints are supported as of version 8.0.15 (yet to be released)

https://bugs.mysql.com/bug.php?id=3464

[23 Jan 16:24] Paul Dubois

Posted by developer: Fixed in 8.0.15.

Previously, MySQL permitted a limited form of CHECK constraint syntax, but parsed and ignored it. MySQL now implements the core features of table and column CHECK constraints, for all storage engines. Constraints are defined using CREATE TABLE and ALTER TABLE statements.

score 1 · Answer 7 · answered Feb 04 '19 at 19:50

Update to MySQL 8.0.16 to use checks:

As of MySQL 8.0.16, CREATE TABLE permits the core features of table and column CHECK constraints, for all storage engines. CREATE TABLE permits the following CHECK constraint syntax, for both table constraints and column constraints

MySQL Checks Documentation

score -2 · Answer 8 · edited Jan 29 '12 at 05:44

-2

try with set sql_mode = 'STRICT_TRANS_TABLES' OR SET sql_mode='STRICT_ALL_TABLES'

edited Jan 29 '12 at 05:44

Mark Hall

53,938
9
94
111

answered Jan 29 '12 at 05:02

Kanagu

606
3
9
17

2

that actuall does not help (MySQL 5.6) it prevents entering data of false type but not of entering data that does not meet the ``CHECK`` constraint – petermeissner Aug 28 '13 at 09:42

CHECK constraint in MySQL is not working

8 Answers8

Linked

Related