CHECK constraint in MySQL is not working

Question

First I created a table like  CREATE TABLE Customer     SD integer CHECK  SD  gt  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

User · Answer

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

User · Answer

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 gt  delimiter    mysql gt  CREATE TRIGGER trig sd check BEFORE INSERT ON Customer      - gt  FOR EACH ROW      - gt  BEGIN      - gt  IF NEW SD lt 0 THEN      - gt  SET NEW SD 0       - gt  END IF       - gt  END     - gt     mysql gt  delimiter     Hope that helps

User · Answer

try with set sql mode    STRICT TRANS TABLES  OR SET sql mode  STRICT ALL TABLES

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The CHECK constraint doesn t seem to be implemented in MySQL   See this bug report  https   bugs mysql com bug php id 3464

User · Answer

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    lt  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  quot check constraint quot  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

[mysql] CHECK constraint in MySQL is not working

Examples related to mysql

Examples related to check-constraints