1set @save_check_constraint=@@check_constraint_checks; 2create table t1 (a int check(a>10), b int check (b > 20), constraint `min` check (a+b > 100), constraint `max` check (a+b <500)) engine=myisam; 3show create table t1; 4Table Create Table 5t1 CREATE TABLE `t1` ( 6 `a` int(11) DEFAULT NULL CHECK (`a` > 10), 7 `b` int(11) DEFAULT NULL CHECK (`b` > 20), 8 CONSTRAINT `min` CHECK (`a` + `b` > 100), 9 CONSTRAINT `max` CHECK (`a` + `b` < 500) 10) ENGINE=MyISAM DEFAULT CHARSET=latin1 11insert into t1 values (100,100); 12insert into t1 values (1,1); 13ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1` 14insert into t1 values (20,1); 15ERROR 23000: CONSTRAINT `t1.b` failed for `test`.`t1` 16insert into t1 values (20,30); 17ERROR 23000: CONSTRAINT `min` failed for `test`.`t1` 18insert into t1 values (500,500); 19ERROR 23000: CONSTRAINT `max` failed for `test`.`t1` 20insert into t1 values (101,101),(102,102),(600,600),(103,103); 21ERROR 23000: CONSTRAINT `max` failed for `test`.`t1` 22select * from t1; 23a b 24100 100 25101 101 26102 102 27truncate table t1; 28insert ignore into t1 values (101,101),(102,102),(600,600),(103,103); 29Warnings: 30Warning 4025 CONSTRAINT `max` failed for `test`.`t1` 31select * from t1; 32a b 33101 101 34102 102 35103 103 36set check_constraint_checks=0; 37truncate table t1; 38insert into t1 values (101,101),(102,102),(600,600),(103,103); 39select * from t1; 40a b 41101 101 42102 102 43600 600 44103 103 45set check_constraint_checks=@save_check_constraint; 46alter table t1 add c int default 0 check (c < 10); 47ERROR 23000: CONSTRAINT `max` failed for `test`.`t1` 48set check_constraint_checks=0; 49alter table t1 add c int default 0 check (c < 10); 50alter table t1 add check (a+b+c < 500); 51set check_constraint_checks=@save_check_constraint; 52show create table t1; 53Table Create Table 54t1 CREATE TABLE `t1` ( 55 `a` int(11) DEFAULT NULL CHECK (`a` > 10), 56 `b` int(11) DEFAULT NULL CHECK (`b` > 20), 57 `c` int(11) DEFAULT 0 CHECK (`c` < 10), 58 CONSTRAINT `min` CHECK (`a` + `b` > 100), 59 CONSTRAINT `max` CHECK (`a` + `b` < 500), 60 CONSTRAINT `CONSTRAINT_1` CHECK (`a` + `b` + `c` < 500) 61) ENGINE=MyISAM DEFAULT CHARSET=latin1 62insert into t1 values(105,105,105); 63ERROR 23000: CONSTRAINT `t1.c` failed for `test`.`t1` 64insert into t1 values(249,249,9); 65ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` 66insert into t1 values(105,105,9); 67select * from t1; 68a b c 69101 101 0 70102 102 0 71600 600 0 72103 103 0 73105 105 9 74create table t2 like t1; 75show create table t2; 76Table Create Table 77t2 CREATE TABLE `t2` ( 78 `a` int(11) DEFAULT NULL CHECK (`a` > 10), 79 `b` int(11) DEFAULT NULL CHECK (`b` > 20), 80 `c` int(11) DEFAULT 0 CHECK (`c` < 10), 81 CONSTRAINT `min` CHECK (`a` + `b` > 100), 82 CONSTRAINT `max` CHECK (`a` + `b` < 500), 83 CONSTRAINT `CONSTRAINT_1` CHECK (`a` + `b` + `c` < 500) 84) ENGINE=MyISAM DEFAULT CHARSET=latin1 85alter table t2 drop constraint c; 86ERROR 42000: Can't DROP CONSTRAINT `c`; check that it exists 87alter table t2 drop constraint if exists c; 88Warnings: 89Note 1091 Can't DROP CONSTRAINT `c`; check that it exists 90alter table t2 drop constraint min; 91show create table t2; 92Table Create Table 93t2 CREATE TABLE `t2` ( 94 `a` int(11) DEFAULT NULL CHECK (`a` > 10), 95 `b` int(11) DEFAULT NULL CHECK (`b` > 20), 96 `c` int(11) DEFAULT 0 CHECK (`c` < 10), 97 CONSTRAINT `max` CHECK (`a` + `b` < 500), 98 CONSTRAINT `CONSTRAINT_1` CHECK (`a` + `b` + `c` < 500) 99) ENGINE=MyISAM DEFAULT CHARSET=latin1 100drop table t1,t2; 101create or replace table t1 (a int, b int, constraint check (a>b)); 102show create table t1; 103Table Create Table 104t1 CREATE TABLE `t1` ( 105 `a` int(11) DEFAULT NULL, 106 `b` int(11) DEFAULT NULL, 107 CONSTRAINT `CONSTRAINT_1` CHECK (`a` > `b`) 108) ENGINE=MyISAM DEFAULT CHARSET=latin1 109create or replace table t1 (a int, b int, 110constraint CONSTRAINT_1 check (a>1), 111constraint check (b>1)); 112show create table t1; 113Table Create Table 114t1 CREATE TABLE `t1` ( 115 `a` int(11) DEFAULT NULL, 116 `b` int(11) DEFAULT NULL, 117 CONSTRAINT `CONSTRAINT_1` CHECK (`a` > 1), 118 CONSTRAINT `CONSTRAINT_2` CHECK (`b` > 1) 119) ENGINE=MyISAM DEFAULT CHARSET=latin1 120create or replace table t1 (a int, b int, 121constraint CONSTRAINT_1 check (a>1), 122constraint check (b>1), 123constraint CONSTRAINT_2 check (a>b)); 124show create table t1; 125Table Create Table 126t1 CREATE TABLE `t1` ( 127 `a` int(11) DEFAULT NULL, 128 `b` int(11) DEFAULT NULL, 129 CONSTRAINT `CONSTRAINT_1` CHECK (`a` > 1), 130 CONSTRAINT `CONSTRAINT_3` CHECK (`b` > 1), 131 CONSTRAINT `CONSTRAINT_2` CHECK (`a` > `b`) 132) ENGINE=MyISAM DEFAULT CHARSET=latin1 133drop table t1; 134create table t1(c1 int, c2 int as (c1 + 1), check (c2 > 2)); 135insert into t1(c1) values(1); 136ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` 137insert into t1(c1) values(2); 138drop table t1; 139create or replace table t1( c1 int auto_increment primary key, check( c1 > 0 or c1 is null ) ); 140ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the CHECK clause of `c1` 141create table t1 (a int check (@b in (select user from mysql.user))); 142ERROR HY000: Function or expression 'select ...' cannot be used in the CHECK clause of `a` 143create table t1 (a int check (a > @b)); 144ERROR HY000: Function or expression '@b' cannot be used in the CHECK clause of `a` 145create table t1 (a int check (a = 1)); 146insert t1 values (1); 147insert t1 values (2); 148ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1` 149insert t1 values (NULL); 150select * from t1; 151a 1521 153NULL 154drop table t1; 155create table t1 (id int auto_increment primary key, datecol datetime, check (datecol>'0001-01-01 00:00:00')); 156insert into t1 (datecol) values (now()); 157insert into t1 (datecol) values (now()); 158drop table t1; 159CREATE TABLE t1 ( 160EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 161FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) 162); 163INSERT INTO t1 VALUES (NULL, 'Ken'); 164ERROR 22007: Truncated incorrect DOUBLE value: 'Ken' 165SHOW WARNINGS; 166Level Code Message 167Error 1292 Truncated incorrect DOUBLE value: 'Ken' 168Error 4025 CONSTRAINT `t1.FirstName` failed for `test`.`t1` 169INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); 170ERROR 22007: Truncated incorrect DOUBLE value: 'Ken' 171SHOW WARNINGS; 172Level Code Message 173Error 1292 Truncated incorrect DOUBLE value: 'Ken' 174Error 4025 CONSTRAINT `t1.FirstName` failed for `test`.`t1` 175INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'); 176Warnings: 177Warning 1292 Truncated incorrect DOUBLE value: 'Ken' 178INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); 179Warnings: 180Warning 1292 Truncated incorrect DOUBLE value: 'Ken' 181Warning 1292 Truncated incorrect DOUBLE value: 'Brian' 182set sql_mode=""; 183INSERT INTO t1 VALUES (NULL, 'Ken'); 184Warnings: 185Warning 1292 Truncated incorrect DOUBLE value: 'Ken' 186INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); 187Warnings: 188Warning 1292 Truncated incorrect DOUBLE value: 'Ken' 189Warning 1292 Truncated incorrect DOUBLE value: 'Brian' 190set sql_mode=default; 191select * from t1; 192EmployeeID FirstName 1931 Ken 1942 Ken 1953 Brian 1964 Ken 1975 Ken 1986 Brian 199drop table t1; 200# 201# MDEV-16630: Ambiguous error message when check constraint 202# matches table name 203# 204use test; 205drop table if exists t; 206create table t(a int, b int check(b>0), 207constraint b check(a<b), constraint a check(a>0), 208constraint x check (a>10)); 209show create table t; 210Table Create Table 211t CREATE TABLE `t` ( 212 `a` int(11) DEFAULT NULL, 213 `b` int(11) DEFAULT NULL CHECK (`b` > 0), 214 CONSTRAINT `b` CHECK (`a` < `b`), 215 CONSTRAINT `a` CHECK (`a` > 0), 216 CONSTRAINT `x` CHECK (`a` > 10) 217) ENGINE=MyISAM DEFAULT CHARSET=latin1 218# Field constraint 'b' will fail 219insert into t values (-1, 0); 220ERROR 23000: CONSTRAINT `t.b` failed for `test`.`t` 221# Table constraint 'b' will fail 222insert into t values (1,1); 223ERROR 23000: CONSTRAINT `b` failed for `test`.`t` 224drop table t; 225create table t1 (a int auto_increment primary key, b int, check (b > 5)); 226insert t1 (b) values (1); 227ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` 228insert t1 (b) values (10); 229select * from t1 where a is null; 230a b 231set sql_auto_is_null=1; 232select * from t1 where a is null; 233a b 2341 10 235insert t1 (b) values (1); 236ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` 237drop table t1; 238# 239# MDEV-24274 ALTER TABLE with CHECK CONSTRAINTS gives "Out of Memory" error 240# 241create table t1 (id varchar(2), constraint id check (id regexp '[a-z]')); 242alter table t1 force; 243show create table t1; 244Table Create Table 245t1 CREATE TABLE `t1` ( 246 `id` varchar(2) DEFAULT NULL, 247 CONSTRAINT `id` CHECK (`id` regexp '[a-z]') 248) ENGINE=MyISAM DEFAULT CHARSET=latin1 249drop table t1; 250