1#------------------------------------------------------------------------ 2# Test cases to verify column check constraint syntax. 3#------------------------------------------------------------------------ 4CREATE TABLE t1(f1 int CHECK); 5ERROR 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 ')' at line 1 6CREATE TABLE t1(f1 int CHECK()); 7ERROR 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 '))' at line 1 8CREATE TABLE t1(f1 int CONSTRAINT CHECK()); 9ERROR 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 '))' at line 1 10CREATE TABLE t1(f1 int t1_ck CHECK()); 11ERROR 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 't1_ck CHECK())' at line 1 12CREATE TABLE t1(f1 int CONSTRAINT t1_ck CHECK()); 13ERROR 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 '))' at line 1 14CREATE TABLE t1(f1 int CONSTRAINT t1_ck CHECK( f1 < 10) NOT); 15ERROR 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 ')' at line 1 16CREATE TABLE t1(f1 int CHECK(f1)); 17ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. 18CREATE TABLE t1(f1 int CHECK(f1 + 10)); 19ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_1'. 20CREATE TABLE t1(f1 int CHECK(f2 < 10)); 21ERROR HY000: Column check constraint 't1_chk_1' references other column. 22CREATE TABLE t1 (f1 int CHECK(f1 < 10), 23f2 int CONSTRAINT t1_f2_ck CHECK (f2 < 10)); 24SHOW CREATE TABLE t1; 25Table Create Table 26t1 CREATE TABLE `t1` ( 27 `f1` int DEFAULT NULL, 28 `f2` int DEFAULT NULL, 29 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), 30 CONSTRAINT `t1_f2_ck` CHECK ((`f2` < 10)) 31) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 32DROP TABLE t1; 33CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CHECK); 34ERROR 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 ')' at line 1 35CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CHECK()); 36ERROR 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 '))' at line 1 37CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CONSTRAINT CHECK()); 38ERROR 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 '))' at line 1 39CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int t1_f2_ck CHECK(f2 < 10)); 40ERROR 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 't1_f2_ck CHECK(f2 < 10))' at line 1 41CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CONSTRAINT t1_f2_ck CHECK(f2 < 10) NOT); 42ERROR 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 ')' at line 1 43CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CHECK(f2 < 10) NOT); 44ERROR 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 ')' at line 1 45CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CHECK(f2)); 46ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_2'. 47CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CHECK(f2 + 10)); 48ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_chk_2'. 49CREATE TABLE t1(f1 int CHECK(f1 < 10), f2 int CHECK(f3 < 10)); 50ERROR HY000: Column check constraint 't1_chk_2' references other column. 51CREATE TABLE t1 (f1 int CHECK(f1 < 10), f2 int CHECK(f2 < 10), 52f3 int CONSTRAINT t1_f3_ck CHECK (f3 < 10)); 53SHOW CREATE TABLE t1; 54Table Create Table 55t1 CREATE TABLE `t1` ( 56 `f1` int DEFAULT NULL, 57 `f2` int DEFAULT NULL, 58 `f3` int DEFAULT NULL, 59 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), 60 CONSTRAINT `t1_chk_2` CHECK ((`f2` < 10)), 61 CONSTRAINT `t1_f3_ck` CHECK ((`f3` < 10)) 62) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 63DROP TABLE t1; 64#------------------------------------------------------------------------ 65# Test cases to verify table check constraint syntax. 66#------------------------------------------------------------------------ 67CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK); 68ERROR 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 ')' at line 1 69CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(); 70ERROR 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 ')' at line 1 71CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK()); 72ERROR 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 '))' at line 1 73CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1)); 74ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_ck'. 75CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1 + 10)); 76ERROR HY000: An expression of non-boolean type specified to a check constraint 't1_ck'. 77CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f2 < 10)); 78ERROR HY000: Check constraint 't1_ck' refers to non-existing column 'f2'. 79CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1 < 10)); 80SHOW CREATE TABLE t1; 81Table Create Table 82t1 CREATE TABLE `t1` ( 83 `f1` int DEFAULT NULL, 84 CONSTRAINT `t1_ck` CHECK ((`f1` < 10)) 85) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 86DROP TABLE t1; 87CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK); 88ERROR 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 ')' at line 1 89CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(); 90ERROR 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 ')' at line 1 91CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f2 > 0) NOT); 92ERROR 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 ')' at line 1 93CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1)); 94ERROR HY000: An expression of non-boolean type specified to a check constraint 't2_ck'. 95CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 + 10)); 96ERROR HY000: An expression of non-boolean type specified to a check constraint 't2_ck'. 97CREATE TABLE t1(f1 int, CONSTRAINT t1_ck CHECK(f1<10), CONSTRAINT t2_ck CHECK(f2 > 1)); 98ERROR HY000: Check constraint 't2_ck' refers to non-existing column 'f2'. 99CREATE TABLE t1(f1 int, CHECK(f1<10), CONSTRAINT t2_ck CHECK(f1 > 1)); 100SHOW CREATE TABLE t1; 101Table Create Table 102t1 CREATE TABLE `t1` ( 103 `f1` int DEFAULT NULL, 104 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), 105 CONSTRAINT `t2_ck` CHECK ((`f1` > 1)) 106) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 107DROP TABLE t1; 108#----------------------------------------------------------------------- 109# Test case to verify check constraint name with special charecters. 110#----------------------------------------------------------------------- 111CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT `ck_1$` CHECK (c2 < 10)); 112SHOW CREATE TABLE t1; 113Table Create Table 114t1 CREATE TABLE `t1` ( 115 `c1` int DEFAULT NULL, 116 `c2` int DEFAULT NULL, 117 CONSTRAINT `ck_1$` CHECK ((`c2` < 10)) 118) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 119DROP TABLE t1; 120#----------------------------------------------------------------------- 121# Test case to verify check constraint name with white spaces. 122#----------------------------------------------------------------------- 123CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT ` ck_2$ ` CHECK (c2 < 10)); 124SHOW CREATE TABLE t1; 125Table Create Table 126t1 CREATE TABLE `t1` ( 127 `c1` int DEFAULT NULL, 128 `c2` int DEFAULT NULL, 129 CONSTRAINT ` ck_2$ ` CHECK ((`c2` < 10)) 130) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 131ALTER TABLE t1 DROP CHECK ck_2$; 132ERROR HY000: Check constraint 'ck_2$' is not found in the table. 133ALTER TABLE t1 DROP CHECK ` ck_2$ `; 134SHOW CREATE TABLE t1; 135Table Create Table 136t1 CREATE TABLE `t1` ( 137 `c1` int DEFAULT NULL, 138 `c2` int DEFAULT NULL 139) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 140ALTER TABLE t1 ADD COLUMN c3 INTEGER , ADD CONSTRAINT ` c 3 ` CHECK ( c3 > 10 ); 141SHOW CREATE TABLE t1; 142Table Create Table 143t1 CREATE TABLE `t1` ( 144 `c1` int DEFAULT NULL, 145 `c2` int DEFAULT NULL, 146 `c3` int DEFAULT NULL, 147 CONSTRAINT ` c 3 ` CHECK ((`c3` > 10)) 148) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 149DROP TABLE t1; 150#----------------------------------------------------------------------- 151# Test case to verify check constraint name with reserved words. 152#----------------------------------------------------------------------- 153CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT FLOAT CHECK (c2 < 10)); 154ERROR 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 'FLOAT CHECK (c2 < 10))' at line 1 155CREATE TABLE t1(c1 INT, c2 INT, CONSTRAINT `FLOAT` CHECK (c2 < 10)); 156SHOW CREATE TABLE t1; 157Table Create Table 158t1 CREATE TABLE `t1` ( 159 `c1` int DEFAULT NULL, 160 `c2` int DEFAULT NULL, 161 CONSTRAINT `FLOAT` CHECK ((`c2` < 10)) 162) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 163DROP TABLE t1; 164#----------------------------------------------------------------------- 165# Test case to verify check constraint with long name. 166#----------------------------------------------------------------------- 167CREATE TABLE t1(c1 INT, c2 INT, 168CONSTRAINT ckkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk 169CHECK (c2 < 10)); 170SHOW CREATE TABLE t1; 171Table Create Table 172t1 CREATE TABLE `t1` ( 173 `c1` int DEFAULT NULL, 174 `c2` int DEFAULT NULL, 175 CONSTRAINT `ckkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk` CHECK ((`c2` < 10)) 176) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 177CREATE TABLE t2(c1 INT, c2 INT, 178CONSTRAINT ckkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk1 179CHECK (c2 < 10)); 180ERROR 42000: Identifier name 'ckkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk1' is too long 181DROP TABLE t1; 182#----------------------------------------------------------------------- 183# Test case to verify check constraint with too long generated name. 184#----------------------------------------------------------------------- 185CREATE TABLE t1 (f1 INT CHECK (f1 < 10)); 186RENAME TABLE t1 TO t123456789012345678901234567890123456789012345678901234567890; 187ERROR 42000: Identifier name 't123456789012345678901234567890123456789012345678901234567890_chk_1' is too long 188DROP TABLE t1; 189CREATE TABLE t123456789012345678901234567890123456789012345678901234567890(f1 INT CHECK(f1 < 10)); 190ERROR 42000: Identifier name 't123456789012345678901234567890123456789012345678901234567890_chk_1' is too long 191CREATE TABLE t123456789012345678901234567890123456789012345678901234567890(f1 INT); 192ALTER TABLE t123456789012345678901234567890123456789012345678901234567890 ADD CONSTRAINT CHECK (f1 < 10); 193ERROR 42000: Identifier name 't123456789012345678901234567890123456789012345678901234567890_chk_1' is too long 194DROP TABLE t123456789012345678901234567890123456789012345678901234567890; 195#----------------------------------------------------------------------- 196# Test case to verify duplicate check constraint name under same 197# database. Check constraints with same name are not allowed under 198# same database. 199#----------------------------------------------------------------------- 200CREATE TABLE t(c1 INT CONSTRAINT t2_chk_1 CHECK (c1 > 10)); 201CREATE TABLE t1(c1 INT CHECK (c1 > 10), CONSTRAINT ck CHECK(c1 > 10)); 202CREATE TABLE t2(c1 INT, CONSTRAINT ck CHECK(c1 > 10)); 203ERROR HY000: Duplicate check constraint name 'ck'. 204ALTER TABLE t1 ADD CONSTRAINT ck CHECK(c1 > 10); 205ERROR HY000: Duplicate check constraint name 'ck'. 206ALTER TABLE t1 RENAME TO t2; 207ERROR HY000: Duplicate check constraint name 't2_chk_1'. 208ALTER TABLE t1 ADD c2 INT, RENAME TO t2; 209ERROR HY000: Duplicate check constraint name 't2_chk_1'. 210DROP TABLE t; 211CREATE DATABASE db1; 212CREATE TABLE db1.t(c1 INT CONSTRAINT t2_chk_1 CHECK (c1 > 10)); 213ALTER TABLE t1 ADD c2 INT, RENAME TO db1.t2; 214ERROR HY000: Duplicate check constraint name 't2_chk_1'. 215ALTER TABLE t1 RENAME TO db1.t2; 216ERROR HY000: Duplicate check constraint name 't2_chk_1'. 217DROP DATABASE db1; 218DROP TABLE t1; 219#----------------------------------------------------------------------- 220# Check constraint names are case insenitive and accent sensitive. Test 221# case to verify the same. 222#----------------------------------------------------------------------- 223CREATE TABLE t1 (f1 INT, 224CONSTRAINT cafe CHECK (f1 < 10), 225CONSTRAINT CAFE CHECK (f1 < 10)); 226ERROR HY000: Duplicate check constraint name 'CAFE'. 227create table t1 (f1 int, 228CONSTRAINT cafe CHECK (f1 < 10), 229CONSTRAINT café CHECK (f1 < 10)); 230SHOW CREATE TABLE t1; 231Table Create Table 232t1 CREATE TABLE `t1` ( 233 `f1` int DEFAULT NULL, 234 CONSTRAINT `cafe` CHECK ((`f1` < 10)), 235 CONSTRAINT `café` CHECK ((`f1` < 10)) 236) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 237DROP TABLE t1; 238#------------------------------------------------------------------------ 239# Test cases to verify forward reference of columns in the constraint. 240#------------------------------------------------------------------------ 241CREATE TABLE t1(CHECK((f1 + f3) > 10), f1 int CHECK (f1 < 10), f2 int); 242ERROR HY000: Check constraint 't1_chk_1' refers to non-existing column 'f3'. 243CREATE TABLE t1(CHECK((f1 + f2) > 10), f1 int CHECK (f1 < 10), f2 int); 244SHOW CREATE TABLE t1; 245Table Create Table 246t1 CREATE TABLE `t1` ( 247 `f1` int DEFAULT NULL, 248 `f2` int DEFAULT NULL, 249 CONSTRAINT `t1_chk_1` CHECK (((`f1` + `f2`) > 10)), 250 CONSTRAINT `t1_chk_2` CHECK ((`f1` < 10)) 251) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 252SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 253CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 254def test t1_chk_1 ((`f1` + `f2`) > 10) 255def test t1_chk_2 (`f1` < 10) 256DROP TABLE t1; 257#----------------------------------------------------------------------- 258# Test case to verify creation of multiple check constraint on table. 259#----------------------------------------------------------------------- 260CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT); 261ALTER TABLE t1 ADD CONSTRAINT ck11 CHECK(c1 > 1), 262ADD CONSTRAINT ck12 CHECK(c1 < 1), 263ADD CONSTRAINT ck21 CHECK(c2 > 1), 264ADD CONSTRAINT ck22 CHECK(c2 < 1), 265ADD CONSTRAINT ck31 CHECK(c3 > 1), 266ADD CONSTRAINT ck32 CHECK(c3 < 1), 267ADD CONSTRAINT ck41 CHECK(c4 > 1), 268ADD CONSTRAINT ck42 CHECK(c4 < 1); 269SHOW CREATE TABLE t1; 270Table Create Table 271t1 CREATE TABLE `t1` ( 272 `c1` int DEFAULT NULL, 273 `c2` int DEFAULT NULL, 274 `c3` int DEFAULT NULL, 275 `c4` int DEFAULT NULL, 276 CONSTRAINT `ck11` CHECK ((`c1` > 1)), 277 CONSTRAINT `ck12` CHECK ((`c1` < 1)), 278 CONSTRAINT `ck21` CHECK ((`c2` > 1)), 279 CONSTRAINT `ck22` CHECK ((`c2` < 1)), 280 CONSTRAINT `ck31` CHECK ((`c3` > 1)), 281 CONSTRAINT `ck32` CHECK ((`c3` < 1)), 282 CONSTRAINT `ck41` CHECK ((`c4` > 1)), 283 CONSTRAINT `ck42` CHECK ((`c4` < 1)) 284) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 285DROP TABLE t1; 286#----------------------------------------------------------------------- 287# Test case to verify check constraints with generated columns 288#----------------------------------------------------------------------- 289CREATE TABLE t1(c1 INT, 290c2 INT, 291c3 INT GENERATED ALWAYS AS (c1 + c2), 292CONSTRAINT ck CHECK (c3 > 10) 293); 294SHOW CREATE TABLE t1; 295Table Create Table 296t1 CREATE TABLE `t1` ( 297 `c1` int DEFAULT NULL, 298 `c2` int DEFAULT NULL, 299 `c3` int GENERATED ALWAYS AS ((`c1` + `c2`)) VIRTUAL, 300 CONSTRAINT `ck` CHECK ((`c3` > 10)) 301) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 302INSERT INTO t1(c1,c2) VALUES(1,1); 303ERROR HY000: Check constraint 'ck' is violated. 304INSERT INTO t1(c1,c2) VALUES(10,10); 305DROP TABLE t1; 306#------------------------------------------------------------------------ 307# Test case to verify check constraints with DEFAULT column value. 308#------------------------------------------------------------------------ 309CREATE TABLE t1(c1 INT DEFAULT 100 CHECK(c1 > 10)); 310INSERT INTO t1() VALUES(1); 311ERROR HY000: Check constraint 't1_chk_1' is violated. 312INSERT INTO t1() VALUES(); 313DROP TABLE t1; 314CREATE TABLE t1(c1 int DEFAULT 1, CONSTRAINT CHECK(c1 IS NOT NULL)); 315INSERT INTO t1() VALUES(); 316INSERT INTO t1() VALUES(NULL); 317ERROR HY000: Check constraint 't1_chk_1' is violated. 318DROP TABLE t1; 319#----------------------------------------------------------------------- 320# Test case to verify check constraint behaviour with ascii charset 321#----------------------------------------------------------------------- 322CREATE TABLE t1(c1 VARCHAR(1) CHARSET ASCII CHECK(c1 = 'a'), 323c2 VARCHAR(1) CHARSET ASCII DEFAULT('b')); 324SHOW CREATE TABLE t1; 325Table Create Table 326t1 CREATE TABLE `t1` ( 327 `c1` varchar(1) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL, 328 `c2` varchar(1) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT (_utf8mb4'b'), 329 CONSTRAINT `t1_chk_1` CHECK ((`c1` = _utf8mb4'a')) 330) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 331INSERT INTO t1(c1) VALUES('b'); 332ERROR HY000: Check constraint 't1_chk_1' is violated. 333INSERT INTO t1(c1) VALUES('a'); 334DROP TABLE t1; 335#----------------------------------------------------------------------- 336# Test case to verify check constraint with an expression evaluated to 337# FALSE always. 338#----------------------------------------------------------------------- 339CREATE TABLE t1 (CHECK (1 < 1), f1 int); 340SHOW CREATE TABLE t1; 341Table Create Table 342t1 CREATE TABLE `t1` ( 343 `f1` int DEFAULT NULL, 344 CONSTRAINT `t1_chk_1` CHECK ((1 < 1)) 345) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 346INSERT INTO t1 VALUES(1); 347ERROR HY000: Check constraint 't1_chk_1' is violated. 348INSERT INTO t1 VALUES(10); 349ERROR HY000: Check constraint 't1_chk_1' is violated. 350INSERT INTO t1 VALUES(); 351ERROR HY000: Check constraint 't1_chk_1' is violated. 352DROP TABLE t1; 353#------------------------------------------------------------------------ 354# Test case to verify INFORMATION_SCHEMA.CHECK_CONSTRAINTS and 355# INFORMATION_SCHEMA.TABLE_CONSTRAINTS result set. 356#------------------------------------------------------------------------ 357CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT CHECK (f2 < 10), 358CONSTRAINT t2_cc1 CHECK (f1 + SQRT(f2) > 6174)); 359SHOW CREATE TABLE t1; 360Table Create Table 361t1 CREATE TABLE `t1` ( 362 `f1` int NOT NULL, 363 `f2` int DEFAULT NULL, 364 PRIMARY KEY (`f1`), 365 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 366 CONSTRAINT `t2_cc1` CHECK (((`f1` + sqrt(`f2`)) > 6174)) 367) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 368SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 369CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 370def test t1_chk_1 (`f2` < 10) 371def test t2_cc1 ((`f1` + sqrt(`f2`)) > 6174) 372SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1'; 373CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED 374def test PRIMARY test t1 PRIMARY KEY YES 375def test t1_chk_1 test t1 CHECK YES 376def test t2_cc1 test t1 CHECK YES 377DROP TABLE t1; 378#------------------------------------------------------------------------ 379# Test cases to verify check constraints in temporary table. 380#------------------------------------------------------------------------ 381CREATE TEMPORARY TABLE tmp_t1(CHECK((f1 + f2) > 10), f1 int CHECK (f1 < 12), 382f2 int); 383SHOW CREATE TABLE tmp_t1; 384Table Create Table 385tmp_t1 CREATE TEMPORARY TABLE `tmp_t1` ( 386 `f1` int DEFAULT NULL, 387 `f2` int DEFAULT NULL, 388 CONSTRAINT `tmp_t1_chk_1` CHECK (((`f1` + `f2`) > 10)), 389 CONSTRAINT `tmp_t1_chk_2` CHECK ((`f1` < 12)) 390) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 391SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 392CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 393DROP TABLE tmp_t1; 394#------------------------------------------------------------------------ 395# Test cases to verify check constraints with CREATE TABLE LIKE 396#------------------------------------------------------------------------ 397CREATE TABLE t1(f1 INT CHECK (f1 < 10), f2 INT, CHECK (f2 < 10), 398CONSTRAINT min CHECK (f1 + f2 > 10), 399CONSTRAINT max CHECK (f1 + f2 < 929)); 400SHOW CREATE TABLE t1; 401Table Create Table 402t1 CREATE TABLE `t1` ( 403 `f1` int DEFAULT NULL, 404 `f2` int DEFAULT NULL, 405 CONSTRAINT `max` CHECK (((`f1` + `f2`) < 929)), 406 CONSTRAINT `min` CHECK (((`f1` + `f2`) > 10)), 407 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), 408 CONSTRAINT `t1_chk_2` CHECK ((`f2` < 10)) 409) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 410CREATE TABLE t2 LIKE t1; 411SHOW CREATE TABLE t2; 412Table Create Table 413t2 CREATE TABLE `t2` ( 414 `f1` int DEFAULT NULL, 415 `f2` int DEFAULT NULL, 416 CONSTRAINT `t2_chk_1` CHECK (((`f1` + `f2`) < 929)), 417 CONSTRAINT `t2_chk_2` CHECK (((`f1` + `f2`) > 10)), 418 CONSTRAINT `t2_chk_3` CHECK ((`f1` < 10)), 419 CONSTRAINT `t2_chk_4` CHECK ((`f2` < 10)) 420) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 421CREATE TEMPORARY TABLE tmp_t2 LIKE t2; 422SHOW CREATE TABLE tmp_t2; 423Table Create Table 424tmp_t2 CREATE TEMPORARY TABLE `tmp_t2` ( 425 `f1` int DEFAULT NULL, 426 `f2` int DEFAULT NULL, 427 CONSTRAINT `tmp_t2_chk_1` CHECK (((`f1` + `f2`) < 929)), 428 CONSTRAINT `tmp_t2_chk_2` CHECK (((`f1` + `f2`) > 10)), 429 CONSTRAINT `tmp_t2_chk_3` CHECK ((`f1` < 10)), 430 CONSTRAINT `tmp_t2_chk_4` CHECK ((`f2` < 10)) 431) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 432CREATE TABLE t3 LIKE tmp_t2; 433SHOW CREATE TABLE t3; 434Table Create Table 435t3 CREATE TABLE `t3` ( 436 `f1` int DEFAULT NULL, 437 `f2` int DEFAULT NULL, 438 CONSTRAINT `t3_chk_1` CHECK (((`f1` + `f2`) < 929)), 439 CONSTRAINT `t3_chk_2` CHECK (((`f1` + `f2`) > 10)), 440 CONSTRAINT `t3_chk_3` CHECK ((`f1` < 10)), 441 CONSTRAINT `t3_chk_4` CHECK ((`f2` < 10)) 442) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 443DROP TABLE t1, t2, t3, tmp_t2; 444#------------------------------------------------------------------------ 445# Test cases to verify effect of check constraint in DML operations. 446#------------------------------------------------------------------------ 447CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT CHECK (f2 < 10)); 448SHOW CREATE TABLE t1; 449Table Create Table 450t1 CREATE TABLE `t1` ( 451 `f1` int NOT NULL, 452 `f2` int DEFAULT NULL, 453 PRIMARY KEY (`f1`), 454 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)) 455) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 456CREATE TABLE t2(f1 INT, f2 INT); 457INSERT INTO t2 VALUES(101, 1); 458INSERT INTO t2 VALUES(102, NULL); 459INSERT INTO t2 VALUES(103, 1000); 460# INSERT operations. 461INSERT INTO t1 VALUES(1, 1); 462INSERT INTO t1 VALUES(2, NULL); 463INSERT INTO t1 VALUES(3, 1000); 464ERROR HY000: Check constraint 't1_chk_1' is violated. 465INSERT IGNORE INTO t1 VALUES (3, 1000); 466Warnings: 467Warning 3819 Check constraint 't1_chk_1' is violated. 468SELECT * FROM t1; 469f1 f2 4701 1 4712 NULL 472INSERT INTO t1 SELECT * FROM t2; 473ERROR HY000: Check constraint 't1_chk_1' is violated. 474SELECT * FROM t1; 475f1 f2 4761 1 4772 NULL 478INSERT IGNORE INTO t1 SELECT * FROM t2; 479Warnings: 480Warning 3819 Check constraint 't1_chk_1' is violated. 481SELECT * FROM t1; 482f1 f2 4831 1 4842 NULL 485101 1 486102 NULL 487# REPLACE operations. 488REPLACE INTO t1 VALUES(4, 1); 489REPLACE INTO t1 VALUES(5, NULL); 490REPLACE INTO t1 VALUES(6, 100); 491ERROR HY000: Check constraint 't1_chk_1' is violated. 492REPLACE INTO t1 VALUES(2, 10); 493ERROR HY000: Check constraint 't1_chk_1' is violated. 494REPLACE INTO t1 VALUES(2, 2); 495SELECT * FROM t1; 496f1 f2 4971 1 4982 2 4994 1 5005 NULL 501101 1 502102 NULL 503# UPDATE operations. 504UPDATE t1 SET f2 = 2; 505SELECT * FROM t1; 506f1 f2 5071 2 5082 2 5094 2 5105 2 511101 2 512102 2 513UPDATE t1 SET f2 = NULL; 514UPDATE t1 SET f2 = 1000; 515ERROR HY000: Check constraint 't1_chk_1' is violated. 516UPDATE IGNORE t1 SET f2 = 1000; 517Warnings: 518Warning 3819 Check constraint 't1_chk_1' is violated. 519Warning 3819 Check constraint 't1_chk_1' is violated. 520Warning 3819 Check constraint 't1_chk_1' is violated. 521Warning 3819 Check constraint 't1_chk_1' is violated. 522Warning 3819 Check constraint 't1_chk_1' is violated. 523Warning 3819 Check constraint 't1_chk_1' is violated. 524SELECT * FROM t1; 525f1 f2 5261 NULL 5272 NULL 5284 NULL 5295 NULL 530101 NULL 531102 NULL 532DROP TABLE t1, t2; 533# LOAD DATA operations. 534CREATE TABLE t2(f1 INT,f2 INT); 535INSERT INTO t2 VALUES(1,1); 536INSERT INTO t2 VALUES(2,NULL); 537CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT CHECK (f2 < 10)); 538SHOW CREATE TABLE t1; 539Table Create Table 540t1 CREATE TABLE `t1` ( 541 `f1` int NOT NULL, 542 `f2` int DEFAULT NULL, 543 PRIMARY KEY (`f1`), 544 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)) 545) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 546SELECT * FROM t2; 547f1 f2 5481 1 5492 NULL 550SELECT * FROM t2 INTO OUTFILE 'tmp1.txt'; 551LOAD DATA INFILE 'tmp1.txt' INTO TABLE t1; 552SELECT * FROM t1; 553f1 f2 5541 1 5552 NULL 556DELETE FROM t1; 557INSERT INTO t2 VALUES(3,20); 558SELECT * FROM t2; 559f1 f2 5601 1 5612 NULL 5623 20 563SELECT * FROM t2 INTO OUTFILE 'tmp2.txt'; 564LOAD DATA INFILE 'tmp2.txt' INTO TABLE t1; 565ERROR HY000: Check constraint 't1_chk_1' is violated. 566SELECT * FROM t1; 567f1 f2 568LOAD DATA INFILE 'tmp2.txt' IGNORE INTO TABLE t1; 569Warnings: 570Warning 3819 Check constraint 't1_chk_1' is violated. 571SELECT * FROM t1; 572f1 f2 5731 1 5742 NULL 575DROP TABLE t1,t2; 576CREATE TABLE t1 (a INT CHECK(a < 3), b CHAR(10)) CHARSET latin1; 577LOAD DATA INFILE '../../std_data/loaddata3.dat' IGNORE INTO TABLE t1 578FIELDS TERMINATED BY '' ENCLOSED BY '' IGNORE 1 LINES; 579Warnings: 580Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 581Warning 1262 Row 3 was truncated; it contained more data than there were input columns 582Warning 3819 Check constraint 't1_chk_1' is violated. 583Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 4 584Warning 1262 Row 4 was truncated; it contained more data than there were input columns 585DROP TABLE t1; 586# LOAD XML operations. 587CREATE TABLE t2(f1 INT,f2 INT); 588INSERT INTO t2 VALUES(1,1); 589INSERT INTO t2 VALUES(2,NULL); 590CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT CHECK (f2 < 10)); 591SHOW CREATE TABLE t1; 592Table Create Table 593t1 CREATE TABLE `t1` ( 594 `f1` int NOT NULL, 595 `f2` int DEFAULT NULL, 596 PRIMARY KEY (`f1`), 597 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)) 598) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 599SELECT * FROM t2; 600f1 f2 6011 1 6022 NULL 603LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE t1;; 604SELECT * FROM t1; 605f1 f2 6061 1 6072 NULL 608DELETE FROM t1; 609INSERT INTO t2 VALUES(3,20); 610SELECT * FROM t2; 611f1 f2 6121 1 6132 NULL 6143 20 615LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp2.xml" INTO TABLE t1;; 616ERROR HY000: Check constraint 't1_chk_1' is violated. 617SELECT * FROM t1; 618f1 f2 619LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp2.xml" IGNORE INTO TABLE t1;; 620Warnings: 621Warning 3819 Check constraint 't1_chk_1' is violated. 622SELECT * FROM t1; 623f1 f2 6241 1 6252 NULL 626DROP TABLE t1,t2; 627#----------------------------------------------------------------------- 628# Test case to verify check constraint with INSERT ON DUPLICATE 629#----------------------------------------------------------------------- 630CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT CHECK (f2 < 10)); 631INSERT INTO t1 VALUES (1, 1); 632INSERT INTO t1 VALUES (1, 2) ON DUPLICATE KEY UPDATE f2 = 4; 633SELECT * FROM t1; 634f1 f2 6351 4 636INSERT IGNORE INTO t1 VALUES (1, 1) ON DUPLICATE KEY UPDATE f2 = 20; 637Warnings: 638Warning 3819 Check constraint 't1_chk_1' is violated. 639INSERT INTO t1 VALUES (1, 1) ON DUPLICATE KEY UPDATE f2 = 20; 640ERROR HY000: Check constraint 't1_chk_1' is violated. 641DROP TABLE t1; 642#----------------------------------------------------------------------- 643# Test case to verify check constraints with multi-table update. 644#----------------------------------------------------------------------- 645CREATE TABLE t1(f1 INT, f2 INT CHECK(f2 < 20)); 646INSERT INTO t1 VALUES (4, 4); 647CREATE TABLE t2(f1 INT, f2 INT); 648INSERT INTO t2 VALUES (4, 24); 649UPDATE t1,t2 SET t1.f2 = t1.f2 + 20 WHERE t1.f1 = t2.f1; 650ERROR HY000: Check constraint 't1_chk_1' is violated. 651UPDATE IGNORE t1,t2 SET t1.f2 = t1.f2 + 20 WHERE t1.f1 = t2.f1; 652Warnings: 653Warning 3819 Check constraint 't1_chk_1' is violated. 654DROP TABLE t1, t2; 655CREATE TABLE t1 ( 656`f1` int(10) unsigned NOT NULL auto_increment, 657`f2` int(11) NOT NULL default '0', 658PRIMARY KEY (`f1`) 659); 660Warnings: 661Warning 1681 Integer display width is deprecated and will be removed in a future release. 662Warning 1681 Integer display width is deprecated and will be removed in a future release. 663INSERT INTO t1 VALUES (4433,5424); 664CREATE TABLE t2 ( 665`f3` int(10) unsigned NOT NULL default '0', 666`f4` int(10) unsigned NOT NULL default '0' CHECK (f4 <= 500), 667PRIMARY KEY (`f3`,`f4`) 668); 669Warnings: 670Warning 1681 Integer display width is deprecated and will be removed in a future release. 671Warning 1681 Integer display width is deprecated and will be removed in a future release. 672INSERT INTO t2 VALUES (495,500); 673INSERT INTO t2 VALUES (496,500); 674UPDATE t2,t1 set t2.f4 = t2.f4 + 1; 675ERROR HY000: Check constraint 't2_chk_1' is violated. 676UPDATE IGNORE t2,t1 set t2.f4 = t2.f4 + 1; 677Warnings: 678Warning 3819 Check constraint 't2_chk_1' is violated. 679Warning 3819 Check constraint 't2_chk_1' is violated. 680DROP TABLE t1, t2; 681#------------------------------------------------------------------------ 682# Test cases to verify generated check constraint name updates due to 683# RENAME TABLE operation. 684#------------------------------------------------------------------------ 685CREATE TABLE t1 (f1 INT CHECK(f1 < 10)); 686SHOW CREATE TABLE t1; 687Table Create Table 688t1 CREATE TABLE `t1` ( 689 `f1` int DEFAULT NULL, 690 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)) 691) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 692RENAME TABLE t1 TO t2; 693SHOW CREATE TABLE t2; 694Table Create Table 695t2 CREATE TABLE `t2` ( 696 `f1` int DEFAULT NULL, 697 CONSTRAINT `t2_chk_1` CHECK ((`f1` < 10)) 698) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 699CREATE TABLE t1(f1 INT CHECK (f1>10), f11 INT CHECK (f11 < 1000)); 700SHOW CREATE TABLE t1; 701Table Create Table 702t1 CREATE TABLE `t1` ( 703 `f1` int DEFAULT NULL, 704 `f11` int DEFAULT NULL, 705 CONSTRAINT `t1_chk_1` CHECK ((`f1` > 10)), 706 CONSTRAINT `t1_chk_2` CHECK ((`f11` < 1000)) 707) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 708RENAME TABLE t1 TO t3, t2 TO t1, t3 TO t2; 709SHOW CREATE TABLE t1; 710Table Create Table 711t1 CREATE TABLE `t1` ( 712 `f1` int DEFAULT NULL, 713 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)) 714) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 715SHOW CREATE TABLE t2; 716Table Create Table 717t2 CREATE TABLE `t2` ( 718 `f1` int DEFAULT NULL, 719 `f11` int DEFAULT NULL, 720 CONSTRAINT `t2_chk_1` CHECK ((`f1` > 10)), 721 CONSTRAINT `t2_chk_2` CHECK ((`f11` < 1000)) 722) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 723DROP TABLE t1, t2; 724#------------------------------------------------------------------------ 725# Test case to verify check constraints removal on DROP table operation. 726#------------------------------------------------------------------------ 727CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT CHECK (f2 < 10)); 728SHOW CREATE TABLE t1; 729Table Create Table 730t1 CREATE TABLE `t1` ( 731 `f1` int NOT NULL, 732 `f2` int DEFAULT NULL, 733 PRIMARY KEY (`f1`), 734 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)) 735) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 736SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 737CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 738def test t1_chk_1 (`f2` < 10) 739SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1'; 740CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED 741def test PRIMARY test t1 PRIMARY KEY YES 742def test t1_chk_1 test t1 CHECK YES 743DROP TABLE t1; 744#------------------------------------------------------------------------ 745# Test case to verify check constraints creation with ALTER TABLE ADD 746# CONSTRAINT operation. 747#------------------------------------------------------------------------ 748CREATE TABLE t1 (f1 INT CHECK (f1 < 10)); 749CREATE TEMPORARY TABLE t3(f1 INT CHECK (f1 < 10)); 750ALTER TABLE t1 ADD CONSTRAINT CHECK (f1 > 1), ADD CONSTRAINT `t1_p_ck` CHECK (f1 > 1); 751ALTER TABLE t3 ADD CONSTRAINT CHECK (f1 > 1), ADD CONSTRAINT `t3_p_ck` CHECK (f1 > 1); 752SHOW CREATE TABLE t1; 753Table Create Table 754t1 CREATE TABLE `t1` ( 755 `f1` int DEFAULT NULL, 756 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), 757 CONSTRAINT `t1_chk_2` CHECK ((`f1` > 1)), 758 CONSTRAINT `t1_p_ck` CHECK ((`f1` > 1)) 759) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 760SHOW CREATE TABLE t3; 761Table Create Table 762t3 CREATE TEMPORARY TABLE `t3` ( 763 `f1` int DEFAULT NULL, 764 CONSTRAINT `t3_chk_1` CHECK ((`f1` < 10)), 765 CONSTRAINT `t3_chk_2` CHECK ((`f1` > 1)), 766 CONSTRAINT `t3_p_ck` CHECK ((`f1` > 1)) 767) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 768# Test case to verify check constraint creation with ALTER TABLE ADD 769# constraint and generated name updates with RENAME TO <table> in 770# ALTER operation. 771ALTER TABLE t1 ADD f2 INT CHECK (f2 < 10), RENAME TO t6, ALGORITHM=COPY; 772SHOW CREATE TABLE t6; 773Table Create Table 774t6 CREATE TABLE `t6` ( 775 `f1` int DEFAULT NULL, 776 `f2` int DEFAULT NULL, 777 CONSTRAINT `t1_p_ck` CHECK ((`f1` > 1)), 778 CONSTRAINT `t6_chk_1` CHECK ((`f1` < 10)), 779 CONSTRAINT `t6_chk_2` CHECK ((`f1` > 1)), 780 CONSTRAINT `t6_chk_3` CHECK ((`f2` < 10)) 781) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 782ALTER TABLE t3 ADD f2 INT CHECK (f2 < 10), RENAME TO t7, ALGORITHM=COPY; 783SHOW CREATE TABLE t7; 784Table Create Table 785t7 CREATE TEMPORARY TABLE `t7` ( 786 `f1` int DEFAULT NULL, 787 `f2` int DEFAULT NULL, 788 CONSTRAINT `t7_chk_1` CHECK ((`f1` < 10)), 789 CONSTRAINT `t7_chk_2` CHECK ((`f1` > 1)), 790 CONSTRAINT `t3_p_ck` CHECK ((`f1` > 1)), 791 CONSTRAINT `t7_chk_3` CHECK ((`f2` < 10)) 792) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 793ALTER TABLE t6 RENAME TO t1; 794SHOW CREATE TABLE t1; 795Table Create Table 796t1 CREATE TABLE `t1` ( 797 `f1` int DEFAULT NULL, 798 `f2` int DEFAULT NULL, 799 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), 800 CONSTRAINT `t1_chk_2` CHECK ((`f1` > 1)), 801 CONSTRAINT `t1_chk_3` CHECK ((`f2` < 10)), 802 CONSTRAINT `t1_p_ck` CHECK ((`f1` > 1)) 803) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 804ALTER TABLE t7 RENAME TO t3; 805SHOW CREATE TABLE t3; 806Table Create Table 807t3 CREATE TEMPORARY TABLE `t3` ( 808 `f1` int DEFAULT NULL, 809 `f2` int DEFAULT NULL, 810 CONSTRAINT `t3_chk_1` CHECK ((`f1` < 10)), 811 CONSTRAINT `t3_chk_2` CHECK ((`f1` > 1)), 812 CONSTRAINT `t3_p_ck` CHECK ((`f1` > 1)), 813 CONSTRAINT `t3_chk_3` CHECK ((`f2` < 10)) 814) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 815# Test case to verify add check constraint with INPLACE alter algorithm. 816ALTER TABLE t1 ADD f2 INT CHECK (f2 < 10), ALGORITHM=INPLACE; 817ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 818ALTER TABLE t3 ADD f2 INT CHECK (f2 < 10), ALGORITHM=INPLACE; 819ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 820ALTER TABLE t1 ADD f3 INT CHECK (f3 < 10) NOT ENFORCED, ALGORITHM=INPLACE; 821ALTER TABLE t1 ADD CONSTRAINT CHECK (f2 < 10) NOT ENFORCED, ALGORITHM=INPLACE; 822ALTER TABLE t1 RENAME COLUMN f1 TO f10; 823ERROR HY000: Check constraint 't1_chk_1' uses column 'f1', hence column cannot be dropped or renamed. 824#------------------------------------------------------------------------ 825# Test case to verify check constraints creation with ALTER TABLE DROP 826# CONSTRAINT operation. 827#------------------------------------------------------------------------ 828SHOW CREATE TABLE t3; 829Table Create Table 830t3 CREATE TEMPORARY TABLE `t3` ( 831 `f1` int DEFAULT NULL, 832 `f2` int DEFAULT NULL, 833 CONSTRAINT `t3_chk_1` CHECK ((`f1` < 10)), 834 CONSTRAINT `t3_chk_2` CHECK ((`f1` > 1)), 835 CONSTRAINT `t3_p_ck` CHECK ((`f1` > 1)), 836 CONSTRAINT `t3_chk_3` CHECK ((`f2` < 10)) 837) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 838ALTER TABLE t3 DROP CHECK t3_chk_3; 839SHOW CREATE TABLE t3; 840Table Create Table 841t3 CREATE TEMPORARY TABLE `t3` ( 842 `f1` int DEFAULT NULL, 843 `f2` int DEFAULT NULL, 844 CONSTRAINT `t3_chk_1` CHECK ((`f1` < 10)), 845 CONSTRAINT `t3_chk_2` CHECK ((`f1` > 1)), 846 CONSTRAINT `t3_p_ck` CHECK ((`f1` > 1)) 847) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 848ALTER TABLE t3 DROP CHECK t3_p_ck, ADD CONSTRAINT t3_p_ck CHECK (f1 > 38); 849SHOW CREATE TABLE t3; 850Table Create Table 851t3 CREATE TEMPORARY TABLE `t3` ( 852 `f1` int DEFAULT NULL, 853 `f2` int DEFAULT NULL, 854 CONSTRAINT `t3_chk_1` CHECK ((`f1` < 10)), 855 CONSTRAINT `t3_chk_2` CHECK ((`f1` > 1)), 856 CONSTRAINT `t3_p_ck` CHECK ((`f1` > 38)) 857) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 858#------------------------------------------------------------------------ 859# Test case to verify check constraints alter operations. 860#------------------------------------------------------------------------ 861INSERT INTO t1 VALUES (5, 5, 5); 862ALTER TABLE t1 ALTER CHECK t1_chk_1 NOT ENFORCED, ALGORITHM=INPLACE; 863INSERT INTO t1 VALUES (8, 8, 8); 864ALTER TABLE t1 ALTER CHECK t1_chk_1 ENFORCED, ALGORITHM=INPLACE; 865ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 866ALTER TABLE t1 ALTER CHECK t1_chk_1 ENFORCED, ALGORITHM=COPY; 867ALTER TABLE t1 ALTER CHECK t1_chk_1 ENFORCED, ALGORITHM=INPLACE; 868INSERT INTO t1 VALUES (12, 5, 5); 869ERROR HY000: Check constraint 't1_chk_1' is violated. 870ALTER TABLE t1 ALTER CHECK t1_chk_1 NOT ENFORCED, ALGORITHM=INPLACE; 871INSERT INTO t1 VALUES (12, 5, 5); 872ALTER TABLE t1 ALTER CHECK t1_chk_1 ENFORCED, ALGORITHM=COPY; 873ERROR HY000: Check constraint 't1_chk_1' is violated. 874DROP TABLE t1, t3; 875#----------------------------------------------------------------------- 876# Test case to add check constraint with copy,instant,inplace algorithm 877#----------------------------------------------------------------------- 878CREATE TABLE t1(c1 INT); 879ALTER TABLE t1 ADD CONSTRAINT CHECK (C1 > 10), ALGORITHM=COPY; 880ALTER TABLE t1 ADD CONSTRAINT CHECK (C1 > 10), ALGORITHM=INPLACE; 881ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 882ALTER TABLE t1 ADD CONSTRAINT CHECK (C1 > 10), ALGORITHM=INSTANT; 883ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY. 884SHOW CREATE TABLE t1; 885Table Create Table 886t1 CREATE TABLE `t1` ( 887 `c1` int DEFAULT NULL, 888 CONSTRAINT `t1_chk_1` CHECK ((`C1` > 10)) 889) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 890DROP TABLE t1; 891#----------------------------------------------------------------------- 892# Test case to verify drop check constraint with inplace algorithm. 893#----------------------------------------------------------------------- 894CREATE TABLE t1 (f1 INT CHECK (f1 < 10)); 895SHOW CREATE TABLE t1; 896Table Create Table 897t1 CREATE TABLE `t1` ( 898 `f1` int DEFAULT NULL, 899 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)) 900) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 901ALTER TABLE t1 DROP CHECK t1_chk_1, ALGORITHM=INPLACE; 902SHOW CREATE TABLE t1; 903Table Create Table 904t1 CREATE TABLE `t1` ( 905 `f1` int DEFAULT NULL 906) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 907DROP TABLE t1; 908#----------------------------------------------------------------------- 909# Test case to alter table to add/drop column with the check constraint. 910#----------------------------------------------------------------------- 911CREATE TABLE t1 (c1 INT, CONSTRAINT ck1 CHECK (c1 > 10)); 912ALTER TABLE t1 ADD COLUMN c2 INT, 913ADD CONSTRAINT ck2 CHECK (c2 > 10); 914SHOW CREATE TABLE t1; 915Table Create Table 916t1 CREATE TABLE `t1` ( 917 `c1` int DEFAULT NULL, 918 `c2` int DEFAULT NULL, 919 CONSTRAINT `ck1` CHECK ((`c1` > 10)), 920 CONSTRAINT `ck2` CHECK ((`c2` > 10)) 921) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 922INSERT INTO t1 VALUES(20,10); 923ERROR HY000: Check constraint 'ck2' is violated. 924ALTER TABLE t1 DROP CHECK ck2, DROP COLUMN c2; 925ALTER TABLE t1 ADD COLUMN c3 INT, 926ADD CONSTRAINT ck3 CHECK (c3 < 10); 927ALTER TABLE t1 DROP CHECK ck3, DROP COLUMN c3, 928ADD COLUMN c4 INT, ADD CONSTRAINT ck4 CHECK( c4 > 10); 929SHOW CREATE TABLE t1; 930Table Create Table 931t1 CREATE TABLE `t1` ( 932 `c1` int DEFAULT NULL, 933 `c4` int DEFAULT NULL, 934 CONSTRAINT `ck1` CHECK ((`c1` > 10)), 935 CONSTRAINT `ck4` CHECK ((`c4` > 10)) 936) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 937DROP TABLE t1; 938#----------------------------------------------------------------------- 939# Test case to verify 940# - Creation of check constraint with NOT ENFORCED state. 941# - Listing state of the check constraints with SHOW and 942# INFORMATION_SCHEMA.CHECK_CONSTRAINTS table. 943# - State updates with ALTER TABLE statement to ALTER 944# check constraints. 945#----------------------------------------------------------------------- 946CREATE TABLE t1(f1 INT, 947f2 INT CHECK (f2 < 10), 948f3 INT CHECK (f3 < 10) NOT ENFORCED, 949CONSTRAINT ck CHECK (f1 > 10), 950CONSTRAINT CHECK (f1 > 10) NOT ENFORCED); 951SHOW CREATE TABLE t1; 952Table Create Table 953t1 CREATE TABLE `t1` ( 954 `f1` int DEFAULT NULL, 955 `f2` int DEFAULT NULL, 956 `f3` int DEFAULT NULL, 957 CONSTRAINT `ck` CHECK ((`f1` > 10)), 958 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 959 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)) /*!80016 NOT ENFORCED */, 960 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */ 961) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 962SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 963CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 964def test ck (`f1` > 10) 965def test t1_chk_1 (`f2` < 10) 966def test t1_chk_2 (`f3` < 10) 967def test t1_chk_3 (`f1` > 10) 968# REVOKE check constraint ck. 969ALTER TABLE t1 ALTER CHECK ck NOT ENFORCED; 970SHOW CREATE TABLE t1; 971Table Create Table 972t1 CREATE TABLE `t1` ( 973 `f1` int DEFAULT NULL, 974 `f2` int DEFAULT NULL, 975 `f3` int DEFAULT NULL, 976 CONSTRAINT `ck` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */, 977 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 978 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)) /*!80016 NOT ENFORCED */, 979 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */ 980) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 981SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 982CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 983def test ck (`f1` > 10) 984def test t1_chk_1 (`f2` < 10) 985def test t1_chk_2 (`f3` < 10) 986def test t1_chk_3 (`f1` > 10) 987# ENFORCE check constraint ck. 988ALTER TABLE t1 ALTER CHECK ck ENFORCED; 989SHOW CREATE TABLE t1; 990Table Create Table 991t1 CREATE TABLE `t1` ( 992 `f1` int DEFAULT NULL, 993 `f2` int DEFAULT NULL, 994 `f3` int DEFAULT NULL, 995 CONSTRAINT `ck` CHECK ((`f1` > 10)), 996 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 997 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)) /*!80016 NOT ENFORCED */, 998 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */ 999) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1000SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 1001CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 1002def test ck (`f1` > 10) 1003def test t1_chk_1 (`f2` < 10) 1004def test t1_chk_2 (`f3` < 10) 1005def test t1_chk_3 (`f1` > 10) 1006# Add new constraint in NOT ENFORCED state. 1007ALTER TABLE t1 ADD CONSTRAINT ck1 CHECK(f1<10) NOT ENFORCED; 1008SHOW CREATE TABLE t1; 1009Table Create Table 1010t1 CREATE TABLE `t1` ( 1011 `f1` int DEFAULT NULL, 1012 `f2` int DEFAULT NULL, 1013 `f3` int DEFAULT NULL, 1014 CONSTRAINT `ck` CHECK ((`f1` > 10)), 1015 CONSTRAINT `ck1` CHECK ((`f1` < 10)) /*!80016 NOT ENFORCED */, 1016 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 1017 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)) /*!80016 NOT ENFORCED */, 1018 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */ 1019) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1020SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 1021CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 1022def test ck (`f1` > 10) 1023def test ck1 (`f1` < 10) 1024def test t1_chk_1 (`f2` < 10) 1025def test t1_chk_2 (`f3` < 10) 1026def test t1_chk_3 (`f1` > 10) 1027# ENFORCE check constraint ck1 1028ALTER TABLE t1 ALTER CHECK ck1 ENFORCED; 1029SHOW CREATE TABLE t1; 1030Table Create Table 1031t1 CREATE TABLE `t1` ( 1032 `f1` int DEFAULT NULL, 1033 `f2` int DEFAULT NULL, 1034 `f3` int DEFAULT NULL, 1035 CONSTRAINT `ck` CHECK ((`f1` > 10)), 1036 CONSTRAINT `ck1` CHECK ((`f1` < 10)), 1037 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 1038 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)) /*!80016 NOT ENFORCED */, 1039 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */ 1040) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1041SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 1042CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 1043def test ck (`f1` > 10) 1044def test ck1 (`f1` < 10) 1045def test t1_chk_1 (`f2` < 10) 1046def test t1_chk_2 (`f3` < 10) 1047def test t1_chk_3 (`f1` > 10) 1048# ENFORCE check constraint t1_chk_2 1049ALTER TABLE t1 ALTER CHECK t1_chk_2 ENFORCED; 1050SHOW CREATE TABLE t1; 1051Table Create Table 1052t1 CREATE TABLE `t1` ( 1053 `f1` int DEFAULT NULL, 1054 `f2` int DEFAULT NULL, 1055 `f3` int DEFAULT NULL, 1056 CONSTRAINT `ck` CHECK ((`f1` > 10)), 1057 CONSTRAINT `ck1` CHECK ((`f1` < 10)), 1058 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 1059 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)), 1060 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */ 1061) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1062SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 1063CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 1064def test ck (`f1` > 10) 1065def test ck1 (`f1` < 10) 1066def test t1_chk_1 (`f2` < 10) 1067def test t1_chk_2 (`f3` < 10) 1068def test t1_chk_3 (`f1` > 10) 1069# ADD column check constraint in NOT ENFORCED state. 1070ALTER TABLE t1 ADD f4 INT CONSTRAINT t1_f4_chk CHECK (f4 < 10) NOT ENFORCED; 1071SHOW CREATE TABLE t1; 1072Table Create Table 1073t1 CREATE TABLE `t1` ( 1074 `f1` int DEFAULT NULL, 1075 `f2` int DEFAULT NULL, 1076 `f3` int DEFAULT NULL, 1077 `f4` int DEFAULT NULL, 1078 CONSTRAINT `ck` CHECK ((`f1` > 10)), 1079 CONSTRAINT `ck1` CHECK ((`f1` < 10)), 1080 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 1081 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)), 1082 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */, 1083 CONSTRAINT `t1_f4_chk` CHECK ((`f4` < 10)) /*!80016 NOT ENFORCED */ 1084) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1085SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 1086CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 1087def test ck (`f1` > 10) 1088def test ck1 (`f1` < 10) 1089def test t1_chk_1 (`f2` < 10) 1090def test t1_chk_2 (`f3` < 10) 1091def test t1_chk_3 (`f1` > 10) 1092def test t1_f4_chk (`f4` < 10) 1093# ENFORCE check constraint t1_f4_chk 1094ALTER TABLE t1 ALTER CHECK t1_f4_chk ENFORCED; 1095SHOW CREATE TABLE t1; 1096Table Create Table 1097t1 CREATE TABLE `t1` ( 1098 `f1` int DEFAULT NULL, 1099 `f2` int DEFAULT NULL, 1100 `f3` int DEFAULT NULL, 1101 `f4` int DEFAULT NULL, 1102 CONSTRAINT `ck` CHECK ((`f1` > 10)), 1103 CONSTRAINT `ck1` CHECK ((`f1` < 10)), 1104 CONSTRAINT `t1_chk_1` CHECK ((`f2` < 10)), 1105 CONSTRAINT `t1_chk_2` CHECK ((`f3` < 10)), 1106 CONSTRAINT `t1_chk_3` CHECK ((`f1` > 10)) /*!80016 NOT ENFORCED */, 1107 CONSTRAINT `t1_f4_chk` CHECK ((`f4` < 10)) 1108) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1109SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS ORDER BY CONSTRAINT_NAME; 1110CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 1111def test ck (`f1` > 10) 1112def test ck1 (`f1` < 10) 1113def test t1_chk_1 (`f2` < 10) 1114def test t1_chk_2 (`f3` < 10) 1115def test t1_chk_3 (`f1` > 10) 1116def test t1_f4_chk (`f4` < 10) 1117DROP TABLE t1; 1118#----------------------------------------------------------------------- 1119# Test case to verify alter statement with drop and alter constraint 1120# on non-existing check constraint. 1121#----------------------------------------------------------------------- 1122CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT); 1123ALTER TABLE t1 DROP CHECK ck13; 1124ERROR HY000: Check constraint 'ck13' is not found in the table. 1125ALTER TABLE t1 ALTER CHECK ck13 ENFORCED; 1126ERROR HY000: Check constraint 'ck13' is not found in the table. 1127DROP TABLE t1; 1128#----------------------------------------------------------------------- 1129# Test case to verify alter statement with multiple add, drop, enforce, 1130# revoke check constraints. 1131#----------------------------------------------------------------------- 1132CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT); 1133ALTER TABLE t1 ADD CONSTRAINT ck11 CHECK(c1 > 1), 1134ADD CONSTRAINT ck12 CHECK(c1 < 1), 1135ADD CONSTRAINT ck21 CHECK(c2 > 1), 1136ADD CONSTRAINT ck22 CHECK(c2 < 1), 1137ADD CONSTRAINT ck31 CHECK(c3 > 1), 1138ADD CONSTRAINT ck32 CHECK(c3 < 1), 1139ADD CONSTRAINT ck41 CHECK(c4 > 1), 1140ADD CONSTRAINT ck42 CHECK(c4 < 1); 1141SHOW CREATE TABLE t1; 1142Table Create Table 1143t1 CREATE TABLE `t1` ( 1144 `c1` int DEFAULT NULL, 1145 `c2` int DEFAULT NULL, 1146 `c3` int DEFAULT NULL, 1147 `c4` int DEFAULT NULL, 1148 CONSTRAINT `ck11` CHECK ((`c1` > 1)), 1149 CONSTRAINT `ck12` CHECK ((`c1` < 1)), 1150 CONSTRAINT `ck21` CHECK ((`c2` > 1)), 1151 CONSTRAINT `ck22` CHECK ((`c2` < 1)), 1152 CONSTRAINT `ck31` CHECK ((`c3` > 1)), 1153 CONSTRAINT `ck32` CHECK ((`c3` < 1)), 1154 CONSTRAINT `ck41` CHECK ((`c4` > 1)), 1155 CONSTRAINT `ck42` CHECK ((`c4` < 1)) 1156) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1157ALTER TABLE t1 1158DROP CHECK ck21, ADD CONSTRAINT ck21 CHECK (c1 > 10), 1159DROP CHECK ck22, ADD CONSTRAINT ck22 CHECK (c1 < 10), 1160DROP CHECK ck31, ADD CONSTRAINT ck31 CHECK (c1 > 10), 1161DROP CHECK ck32, ADD CONSTRAINT ck32 CHECK (c1 < 10), 1162DROP CHECK ck41, ADD CONSTRAINT ck41 CHECK (c1 > 10), 1163DROP CHECK ck42, ADD CONSTRAINT ck42 CHECK (c1 < 10), 1164ALTER CHECK ck11 NOT ENFORCED, 1165ALTER CHECK ck12 NOT ENFORCED, 1166ALTER CHECK ck11 ENFORCED; 1167ALTER TABLE t1 DROP CHECK ck11, ALTER CHECK ck11 NOT ENFORCED, 1168ADD CONSTRAINT ck11 CHECK (c1 > 10); 1169ERROR HY000: Check constraint 'ck11' is not found in the table. 1170SHOW CREATE TABLE t1; 1171Table Create Table 1172t1 CREATE TABLE `t1` ( 1173 `c1` int DEFAULT NULL, 1174 `c2` int DEFAULT NULL, 1175 `c3` int DEFAULT NULL, 1176 `c4` int DEFAULT NULL, 1177 CONSTRAINT `ck11` CHECK ((`c1` > 1)), 1178 CONSTRAINT `ck12` CHECK ((`c1` < 1)) /*!80016 NOT ENFORCED */, 1179 CONSTRAINT `ck21` CHECK ((`c1` > 10)), 1180 CONSTRAINT `ck22` CHECK ((`c1` < 10)), 1181 CONSTRAINT `ck31` CHECK ((`c1` > 10)), 1182 CONSTRAINT `ck32` CHECK ((`c1` < 10)), 1183 CONSTRAINT `ck41` CHECK ((`c1` > 10)), 1184 CONSTRAINT `ck42` CHECK ((`c1` < 10)) 1185) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1186DROP TABLE t1; 1187#----------------------------------------------------------------------- 1188# Test case to verify auto-drop of check constraint on column drop. 1189#----------------------------------------------------------------------- 1190CREATE TABLE t1 (f1 INT check (f1 < 10), f2 INT); 1191SHOW CREATE TABLE t1; 1192Table Create Table 1193t1 CREATE TABLE `t1` ( 1194 `f1` int DEFAULT NULL, 1195 `f2` int DEFAULT NULL, 1196 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)) 1197) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1198# Drops check constraint t1_chk_1 too. 1199ALTER TABLE t1 DROP COLUMN f1; 1200ALTER TABLE t1 ADD COLUMN f1 INT check(f1 < 10), 1201ADD CONSTRAINT check(f1 + f2 < 10), 1202ADD CONSTRAINT check(f2 < 10); 1203SHOW CREATE TABLE t1; 1204Table Create Table 1205t1 CREATE TABLE `t1` ( 1206 `f2` int DEFAULT NULL, 1207 `f1` int DEFAULT NULL, 1208 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)), 1209 CONSTRAINT `t1_chk_2` CHECK (((`f1` + `f2`) < 10)), 1210 CONSTRAINT `t1_chk_3` CHECK ((`f2` < 10)) 1211) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1212ALTER TABLE t1 DROP COLUMN f1; 1213ERROR HY000: Check constraint 't1_chk_2' uses column 'f1', hence column cannot be dropped or renamed. 1214ALTER TABLE t1 RENAME COLUMN f1 to f3; 1215ERROR HY000: Check constraint 't1_chk_1' uses column 'f1', hence column cannot be dropped or renamed. 1216# Drops column f1 and constraints t1_chk_1, t1_chk_2. 1217ALTER TABLE t1 DROP CHECK t1_chk_2, DROP COLUMN f1; 1218SHOW CREATE TABLE t1; 1219Table Create Table 1220t1 CREATE TABLE `t1` ( 1221 `f2` int DEFAULT NULL, 1222 CONSTRAINT `t1_chk_3` CHECK ((`f2` < 10)) 1223) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1224DROP TABLE t1; 1225#----------------------------------------------------------------------- 1226# Test case to verify check constraint with MODIFY COLUMN syntax. 1227#----------------------------------------------------------------------- 1228CREATE TABLE t1(c1 CHAR(1), CHECK (c1 > 'A')); 1229SHOW CREATE TABLE t1; 1230Table Create Table 1231t1 CREATE TABLE `t1` ( 1232 `c1` char(1) DEFAULT NULL, 1233 CONSTRAINT `t1_chk_1` CHECK ((`c1` > _utf8mb4'A')) 1234) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1235INSERT INTO t1 VALUES('A'); 1236ERROR HY000: Check constraint 't1_chk_1' is violated. 1237INSERT INTO t1 VALUES('B'); 1238DELETE FROM t1; 1239SHOW CREATE TABLE t1; 1240Table Create Table 1241t1 CREATE TABLE `t1` ( 1242 `c1` char(1) DEFAULT NULL, 1243 CONSTRAINT `t1_chk_1` CHECK ((`c1` > _utf8mb4'A')) 1244) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1245ALTER TABLE t1 MODIFY COLUMN c1 FLOAT(10.3), DROP CHECK t1_chk_1, ADD CONSTRAINT CHECK(C1 > 10.1) ENFORCED; 1246SHOW CREATE TABLE t1; 1247Table Create Table 1248t1 CREATE TABLE `t1` ( 1249 `c1` float DEFAULT NULL, 1250 CONSTRAINT `t1_chk_1` CHECK ((`C1` > 10.1)) 1251) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1252DROP TABLE t1; 1253CREATE TABLE t1 (f1 INT CHECK (f1 = default(f1))); 1254INSERT INTO t1 VALUES (10); 1255ALTER TABLE t1 MODIFY COLUMN f1 INT DEFAULT 20, algorithm=inplace; 1256ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 1257ALTER TABLE t1 ALTER f1 SET DEFAULT 20, algorithm=inplace; 1258ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 1259ALTER TABLE t1 MODIFY COLUMN f1 INT DEFAULT 20, algorithm=copy; 1260ERROR HY000: Check constraint 't1_chk_1' is violated. 1261DROP TABLE t1; 1262#----------------------------------------------------------------------- 1263# Test case to verify check constraint with CHANGE COLUMN syntax. 1264#----------------------------------------------------------------------- 1265CREATE TABLE t1(c1 CHAR(1), CHECK (c1 > 'A')); 1266SHOW CREATE TABLE t1; 1267Table Create Table 1268t1 CREATE TABLE `t1` ( 1269 `c1` char(1) DEFAULT NULL, 1270 CONSTRAINT `t1_chk_1` CHECK ((`c1` > _utf8mb4'A')) 1271) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1272ALTER TABLE t1 CHANGE c1 c1 FLOAT, ALGORITHM=INPLACE; 1273ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. 1274ALTER TABLE t1 DROP CHECK t1_chk_1, CHANGE c1 c2 VARCHAR(20), ADD CONSTRAINT CHECK(c2 > 'B'); 1275SHOW CREATE TABLE t1; 1276Table Create Table 1277t1 CREATE TABLE `t1` ( 1278 `c2` varchar(20) DEFAULT NULL, 1279 CONSTRAINT `t1_chk_1` CHECK ((`c2` > _utf8mb4'B')) 1280) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1281DROP TABLE t1; 1282CREATE TABLE t (a INT, b INT, CHECK(a != b)); 1283INSERT INTO t VALUES (2000000000, 2000000001); 1284ALTER TABLE t CHANGE a a FLOAT, CHANGE b b FLOAT; 1285ERROR HY000: Check constraint 't_chk_1' is violated. 1286ALTER TABLE t ADD CONSTRAINT CHECK(a > b); 1287ERROR HY000: Check constraint 't_chk_2' is violated. 1288DROP TABLE t; 1289#------------------------------------------------------------------------ 1290# Test case to verify check constraints with IN operator. 1291#------------------------------------------------------------------------ 1292CREATE TABLE t1(f1 int CHECK (f1 IN (10, 20, 30)), f2 int, CHECK(f2 IN (100, 120, 450))); 1293SHOW CREATE TABLE t1; 1294Table Create Table 1295t1 CREATE TABLE `t1` ( 1296 `f1` int DEFAULT NULL, 1297 `f2` int DEFAULT NULL, 1298 CONSTRAINT `t1_chk_1` CHECK ((`f1` in (10,20,30))), 1299 CONSTRAINT `t1_chk_2` CHECK ((`f2` in (100,120,450))) 1300) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1301INSERT INTO t1 VALUES(10, 100); 1302INSERT INTO t1 VALUES(15, 100); 1303ERROR HY000: Check constraint 't1_chk_1' is violated. 1304INSERT INTO t1 VALUES(10, 105); 1305ERROR HY000: Check constraint 't1_chk_2' is violated. 1306DROP TABLE t1; 1307#------------------------------------------------------------------------ 1308# Test case to verify check constraints with BETWEEN operator. 1309#------------------------------------------------------------------------ 1310CREATE TABLE t1(f1 int CHECK(f1 BETWEEN 10 AND 30), 1311f2 int, CHECK(f2 BETWEEN 100 AND 450)); 1312SHOW CREATE TABLE t1; 1313Table Create Table 1314t1 CREATE TABLE `t1` ( 1315 `f1` int DEFAULT NULL, 1316 `f2` int DEFAULT NULL, 1317 CONSTRAINT `t1_chk_1` CHECK ((`f1` between 10 and 30)), 1318 CONSTRAINT `t1_chk_2` CHECK ((`f2` between 100 and 450)) 1319) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1320INSERT INTO t1 VALUES(20, 200); 1321INSERT INTO t1 VALUES(2, 200); 1322ERROR HY000: Check constraint 't1_chk_1' is violated. 1323INSERT INTO t1 VALUES(20, 2000); 1324ERROR HY000: Check constraint 't1_chk_2' is violated. 1325DROP TABLE t1; 1326#------------------------------------------------------------------------ 1327# Test case to verify check constraints with IS NOT NULL. 1328#------------------------------------------------------------------------ 1329CREATE TABLE t1 (f1 int CHECK(f1 IS NOT NULL)); 1330SHOW CREATE TABLE t1; 1331Table Create Table 1332t1 CREATE TABLE `t1` ( 1333 `f1` int DEFAULT NULL, 1334 CONSTRAINT `t1_chk_1` CHECK ((`f1` is not null)) 1335) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1336INSERT INTO t1 VALUES(10); 1337INSERT INTO t1 VALUES(NULL); 1338ERROR HY000: Check constraint 't1_chk_1' is violated. 1339DROP TABLE t1; 1340#------------------------------------------------------------------------ 1341# Test case to verify check constraints with IS NULL. 1342#------------------------------------------------------------------------ 1343CREATE TABLE t1 (f1 int CHECK(f1 IS NULL)); 1344SHOW CREATE TABLE t1; 1345Table Create Table 1346t1 CREATE TABLE `t1` ( 1347 `f1` int DEFAULT NULL, 1348 CONSTRAINT `t1_chk_1` CHECK ((`f1` is null)) 1349) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1350INSERT INTO t1 VALUES(NULL); 1351INSERT INTO t1 VALUES(10); 1352ERROR HY000: Check constraint 't1_chk_1' is violated. 1353DROP TABLE t1; 1354#----------------------------------------------------------------------- 1355# Test case to verify check constraint with CASE Statement 1356#----------------------------------------------------------------------- 1357CREATE TABLE t1(c1 INT, c2 INT); 1358ALTER TABLE t1 ADD CONSTRAINT CHECK( (CASE WHEN c1 > 10 THEN c2 = 20 END) = 1); 1359SHOW CREATE TABLE t1; 1360Table Create Table 1361t1 CREATE TABLE `t1` ( 1362 `c1` int DEFAULT NULL, 1363 `c2` int DEFAULT NULL, 1364 CONSTRAINT `t1_chk_1` CHECK (((case when (`c1` > 10) then (`c2` = 20) end) = 1)) 1365) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1366INSERT INTO t1 VALUES(1,1); 1367INSERT INTO t1 VALUES(15,1); 1368ERROR HY000: Check constraint 't1_chk_1' is violated. 1369INSERT INTO t1 VALUES(15,20); 1370SELECT * FROM t1; 1371c1 c2 13721 1 137315 20 1374DROP TABLE t1; 1375#------------------------------------------------------------------------ 1376# Test case to verify check constraints restrictions. 1377#------------------------------------------------------------------------ 1378# Check constraint using column with AUTO_INCREMENT attribute. 1379CREATE TABLE t1 (f1 int primary key auto_increment, f2 int, CHECK (f1 != f2)); 1380ERROR HY000: Check constraint 't1_chk_1' cannot refer to an auto-increment column. 1381CREATE TABLE t1 (f1 int primary key auto_increment CHECK (f1 < 10), f2 int, CHECK (f1 != f2)); 1382ERROR HY000: Check constraint 't1_chk_1' cannot refer to an auto-increment column. 1383# Check constraint using non-deterministic builtin functions. 1384# NOW() 1385CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + NOW() > '2011-11-21')); 1386ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: now. 1387# CURRENT_TIMESTAMP() 1388CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + CURRENT_TIMESTAMP() > '2011-11-21 01:02:03')); 1389ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: now. 1390# CURRENT_TIMESTAMP 1391CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + CURRENT_TIMESTAMP > '2011-11-21 01:02:03')); 1392ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: now. 1393# CURDATE() 1394CREATE TABLE t1 (f1 DATETIME CHECK (f1 + CURDATE() > '2011-11-21')); 1395ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: curdate. 1396# CURTIME() 1397CREATE TABLE t1 (f1 DATETIME CHECK (f1 + CURTIME() > '23:11:21')); 1398ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: curtime. 1399# CURTIME 1400CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + CURRENT_TIME > '01:02:03')); 1401ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: curtime. 1402# CURRENT_DATE() 1403CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + CURRENT_DATE() > '2011-11-21')); 1404ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: curdate. 1405# CURRENT_DATE 1406CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + CURRENT_DATE > '2011-11-21')); 1407ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: curdate. 1408# CURRENT_TIME() 1409CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + CURRENT_TIME() > '01:02:03')); 1410ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: curtime. 1411# CURRENT_TIME 1412CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + CURRENT_TIME > '01:02:03')); 1413ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: curtime. 1414# LOCALTIME() 1415CREATE TABLE t1 (f1 DATETIME CHECK (f1 + LOCALTIME() > '23:11:21')); 1416ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: now. 1417# LOCALTIME 1418CREATE TABLE t1 (f1 DATETIME CHECK (f1 + LOCALTIME > '23:11:21')); 1419ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: now. 1420# LOCALTIMESTAMP() 1421CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + LOCALTIMESTAMP() > '2011-11-21 01:02:03')); 1422ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: now. 1423# LOCALTIMESTAMP 1424CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + LOCALTIMESTAMP > '2011-11-21 01:02:03')); 1425ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: now. 1426# UNIX_TIMESTAMP() 1427CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + UNIX_TIMESTAMP() > '2011-11-21 01:02:03')); 1428ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: unix_timestamp. 1429# UNIX_DATE() 1430CREATE TABLE t1 (f1 DATETIME CHECK (f1 + UTC_DATE() > '2011-11-21')); 1431ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: utc_date. 1432# UNIX_TIMESTAMP() 1433CREATE TABLE t1 (f1 TIMESTAMP CHECK (f1 + UTC_TIMESTAMP() > '2011-11-21 01:02:03')); 1434ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: utc_timestamp. 1435# UNIX_TIME() 1436CREATE TABLE t1 (f1 DATETIME CHECK (f1 + UTC_TIME() > '23:11:21')); 1437ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: utc_time. 1438# CONNECTION_ID 1439CREATE TABLE t1 (f1 INT CHECK (f1 + CONNECTION_ID() < 929)); 1440ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: connection_id. 1441# CURRENT_USER() 1442CREATE TABLE t1 (a VARCHAR(32) CHECK (CURRENT_USER() != a)); 1443ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: current_user. 1444# CURRENT_USER 1445CREATE TABLE t1 (a VARCHAR(32) CHECK (CURRENT_USER != a)); 1446ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: current_user. 1447# SESSION_USER() 1448CREATE TABLE t1 (a VARCHAR(32) CHECK (SESSION_USER() != a)); 1449ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: user. 1450# VERSION() 1451CREATE TABLE t1 (a VARCHAR(32) CHECK (VERSION() != a)); 1452ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: version(). 1453# FOUND_ROWS 1454CREATE TABLE t1 (a VARCHAR(1024), b INT CHECK (b + FOUND_ROWS() > 2000)); 1455ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: found_rows. 1456# LAST_INSERT_ID 1457CREATE TABLE t1 (a INT CHECK ((a + LAST_INSERT_ID()) < 929)); 1458ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: last_insert_id. 1459# SYSTEM_USER 1460CREATE TABLE t1 (a VARCHAR(32) CHECK (SYSTEM_USER() != a)); 1461ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: user. 1462# USER 1463CREATE TABLE t1 (a VARCHAR(32) CHECK (USER() != a)); 1464ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: user. 1465# RAND() 1466CREATE TABLE t1 (f1 FLOAT CHECK (f1 + RAND() < 929.929)); 1467ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: rand. 1468# ROW_COUNT() 1469CREATE TABLE t1 (a INT CHECK (a + ROW_COUNT() > 1000)); 1470ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: row_count. 1471# GET_LOCK() 1472CREATE TABLE t1 (a VARCHAR(1024), b VARCHAR(1024) CHECK (GET_LOCK(b,10) != 0)); 1473ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: get_lock. 1474# IS_FREE_LOCK() 1475CREATE TABLE t1 (a VARCHAR(1024), b VARCHAR(1024) CHECK (IS_FREE_LOCK(b) != 0)); 1476ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: is_free_lock. 1477# IS_USED_LOCK() 1478CREATE TABLE t1 (a VARCHAR(1024), b VARCHAR(1024) CHECK (IS_USED_LOCK(b) != 0)); 1479ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: is_used_lock. 1480# RELEASE_LOCK() 1481CREATE TABLE t1 (a VARCHAR(1024), b VARCHAR(1024) CHECK (RELEASE_LOCK(b) != 0)); 1482ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: release_lock. 1483# RELEASE_ALL_LOCK() 1484CREATE TABLE t1 (a VARCHAR(1024), b VARCHAR(1024), CHECK (RELEASE_ALL_LOCKS() != 0)); 1485ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: release_all_locks. 1486# LOAD_FILE 1487CREATE TABLE t1 (f1 VARCHAR(1024), f2 VARCHAR(1024) CHECK (LOAD_FILE(f2) != NULL)); 1488ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: load_file. 1489# UUID() 1490CREATE TABLE t1 (id CHAR(40) CHECK(UUID() != id)); 1491ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: uuid. 1492# UUID_SHORT 1493CREATE TABLE t1 (id INT CHECK(UUID_SHORT() != id)); 1494ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: uuid_short. 1495# SLEEP 1496CREATE TABLE t1 (id INT CHECK(SLEEP(id) != 0)); 1497ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: sleep. 1498# Stored function 1499CREATE FUNCTION func() RETURNS INT DETERMINISTIC return 1; 1500CREATE TABLE t1 (id INT CHECK(id = func())); 1501ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: `func`. 1502DROP FUNCTION func; 1503# Stored procedure 1504CREATE PROCEDURE proc() SELECT 1; 1505CREATE TABLE t1 (id INT CHECK(id = proc())); 1506ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function: `proc`. 1507DROP PROCEDURE proc; 1508# User variable 1509SET @v = 10; 1510CREATE TABLE t1 (id INT CHECK (id != @v)); 1511ERROR HY000: An expression of a check constraint 't1_chk_1' cannot refer to a user or system variable. 1512# System variables. 1513CREATE TABLE t1 (id INT CHECK (id != @@global.max_execution_time)); 1514ERROR HY000: An expression of a check constraint 't1_chk_1' cannot refer to a user or system variable. 1515CREATE TABLE t1 (id INt CHECK (id != @@session.max_execution_time)); 1516ERROR HY000: An expression of a check constraint 't1_chk_1' cannot refer to a user or system variable. 1517# Subquery 1518CREATE TABLE t1 (id INT CHECK (id != (SELECT 1))); 1519ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function. 1520# Parameter 1521PREPARE stmt FROM 'CREATE TABLE t1 (id INT CHECK(id != ?))'; 1522EXECUTE stmt using @v; 1523ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function. 1524DEALLOCATE PREPARE stmt; 1525#------------------------------------------------------------------------ 1526# Test case to verify check constraints with numeric data types. 1527#------------------------------------------------------------------------ 1528CREATE TABLE t1 ( 1529c1 BIT(7) CHECK(c1 < B'1111100'), 1530c2 BOOLEAN CHECK(c2 > 0), 1531c3 TINYINT CHECK(c3 > 10), 1532c4 SMALLINT CHECK(c4 > 10), 1533c5 MEDIUMINT CHECK(c5 > 10), 1534c6 INT CHECK(c6 > 10), 1535c7 BIGINT CHECK(c7 > 10), 1536c8 DECIMAL(6,2) CHECK(c8 > 10.1), 1537c9 FLOAT(6,2) CHECK(c9 > 10.1), 1538c10 DOUBLE(6,2) CHECK(c10 > 10.1)); 1539Warnings: 1540Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. 1541Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. 1542SHOW CREATE TABLE t1; 1543Table Create Table 1544t1 CREATE TABLE `t1` ( 1545 `c1` bit(7) DEFAULT NULL, 1546 `c2` tinyint(1) DEFAULT NULL, 1547 `c3` tinyint DEFAULT NULL, 1548 `c4` smallint DEFAULT NULL, 1549 `c5` mediumint DEFAULT NULL, 1550 `c6` int DEFAULT NULL, 1551 `c7` bigint DEFAULT NULL, 1552 `c8` decimal(6,2) DEFAULT NULL, 1553 `c9` float(6,2) DEFAULT NULL, 1554 `c10` double(6,2) DEFAULT NULL, 1555 CONSTRAINT `t1_chk_1` CHECK ((`c1` < 0x7c)), 1556 CONSTRAINT `t1_chk_10` CHECK ((`c10` > 10.1)), 1557 CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)), 1558 CONSTRAINT `t1_chk_3` CHECK ((`c3` > 10)), 1559 CONSTRAINT `t1_chk_4` CHECK ((`c4` > 10)), 1560 CONSTRAINT `t1_chk_5` CHECK ((`c5` > 10)), 1561 CONSTRAINT `t1_chk_6` CHECK ((`c6` > 10)), 1562 CONSTRAINT `t1_chk_7` CHECK ((`c7` > 10)), 1563 CONSTRAINT `t1_chk_8` CHECK ((`c8` > 10.1)), 1564 CONSTRAINT `t1_chk_9` CHECK ((`c9` > 10.1)) 1565) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1566INSERT INTO t1(c1) VALUES(B'1111110'); 1567ERROR HY000: Check constraint 't1_chk_1' is violated. 1568INSERT INTO t1(c2) VALUES(0); 1569ERROR HY000: Check constraint 't1_chk_2' is violated. 1570INSERT INTO t1(c3) VALUES(1); 1571ERROR HY000: Check constraint 't1_chk_3' is violated. 1572INSERT INTO t1(c4) VALUES(1); 1573ERROR HY000: Check constraint 't1_chk_4' is violated. 1574INSERT INTO t1(c5) VALUES(1); 1575ERROR HY000: Check constraint 't1_chk_5' is violated. 1576INSERT INTO t1(c6) VALUES(1); 1577ERROR HY000: Check constraint 't1_chk_6' is violated. 1578INSERT INTO t1(c7) VALUES(1); 1579ERROR HY000: Check constraint 't1_chk_7' is violated. 1580INSERT INTO t1(c8) VALUES(10.0); 1581ERROR HY000: Check constraint 't1_chk_8' is violated. 1582INSERT INTO t1(c9) VALUES(10.0); 1583ERROR HY000: Check constraint 't1_chk_9' is violated. 1584INSERT INTO t1(c10) VALUES(10.0); 1585ERROR HY000: Check constraint 't1_chk_10' is violated. 1586INSERT INTO t1(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) 1587VALUES(B'1111000',1,11,11,11,11,11,10.2,10.2,10.2); 1588DROP TABLE t1; 1589#------------------------------------------------------------------------ 1590# Test case to verify check constraints with string data types. 1591#------------------------------------------------------------------------ 1592CREATE TABLE t1(c1 CHAR(1) CHECK(c1 > 'a'), 1593c2 VARCHAR(1) CHECK(c2 > 'a'), 1594c3 BINARY(1) CHECK(c3 > 'a'), 1595c4 VARBINARY(1) CHECK(c4 > 'a'), 1596c5 TINYBLOB CHECK(c5 > 'a'), 1597c6 TINYTEXT CHECK(c6 > 'a'), 1598c7 BLOB CHECK(c7 > 'a'), 1599c8 TEXT CHECK(c8 > 'a'), 1600c9 MEDIUMBLOB CHECK(c9 > 'a'), 1601c10 MEDIUMTEXT CHECK(c10 > 'a'), 1602c11 LONGBLOB CHECK(c11 > 'a'), 1603c12 LONGTEXT CHECK(c12 > 'a')); 1604SHOW CREATE TABLE t1; 1605Table Create Table 1606t1 CREATE TABLE `t1` ( 1607 `c1` char(1) DEFAULT NULL, 1608 `c2` varchar(1) DEFAULT NULL, 1609 `c3` binary(1) DEFAULT NULL, 1610 `c4` varbinary(1) DEFAULT NULL, 1611 `c5` tinyblob, 1612 `c6` tinytext, 1613 `c7` blob, 1614 `c8` text, 1615 `c9` mediumblob, 1616 `c10` mediumtext, 1617 `c11` longblob, 1618 `c12` longtext, 1619 CONSTRAINT `t1_chk_1` CHECK ((`c1` > _utf8mb4'a')), 1620 CONSTRAINT `t1_chk_10` CHECK ((`c10` > _utf8mb4'a')), 1621 CONSTRAINT `t1_chk_11` CHECK ((`c11` > _utf8mb4'a')), 1622 CONSTRAINT `t1_chk_12` CHECK ((`c12` > _utf8mb4'a')), 1623 CONSTRAINT `t1_chk_2` CHECK ((`c2` > _utf8mb4'a')), 1624 CONSTRAINT `t1_chk_3` CHECK ((`c3` > _utf8mb4'a')), 1625 CONSTRAINT `t1_chk_4` CHECK ((`c4` > _utf8mb4'a')), 1626 CONSTRAINT `t1_chk_5` CHECK ((`c5` > _utf8mb4'a')), 1627 CONSTRAINT `t1_chk_6` CHECK ((`c6` > _utf8mb4'a')), 1628 CONSTRAINT `t1_chk_7` CHECK ((`c7` > _utf8mb4'a')), 1629 CONSTRAINT `t1_chk_8` CHECK ((`c8` > _utf8mb4'a')), 1630 CONSTRAINT `t1_chk_9` CHECK ((`c9` > _utf8mb4'a')) 1631) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1632INSERT INTO t1(c1) VALUES('a'); 1633ERROR HY000: Check constraint 't1_chk_1' is violated. 1634INSERT INTO t1(c2) VALUES('a'); 1635ERROR HY000: Check constraint 't1_chk_2' is violated. 1636INSERT INTO t1(c3) VALUES('a'); 1637ERROR HY000: Check constraint 't1_chk_3' is violated. 1638INSERT INTO t1(c4) VALUES('a'); 1639ERROR HY000: Check constraint 't1_chk_4' is violated. 1640INSERT INTO t1(c5) VALUES('a'); 1641ERROR HY000: Check constraint 't1_chk_5' is violated. 1642INSERT INTO t1(c6) VALUES('a'); 1643ERROR HY000: Check constraint 't1_chk_6' is violated. 1644INSERT INTO t1(c7) VALUES('a'); 1645ERROR HY000: Check constraint 't1_chk_7' is violated. 1646INSERT INTO t1(c8) VALUES('a'); 1647ERROR HY000: Check constraint 't1_chk_8' is violated. 1648INSERT INTO t1(c9) VALUES('a'); 1649ERROR HY000: Check constraint 't1_chk_9' is violated. 1650INSERT INTO t1(c10) VALUES('a'); 1651ERROR HY000: Check constraint 't1_chk_10' is violated. 1652INSERT INTO t1(c11) VALUES('a'); 1653ERROR HY000: Check constraint 't1_chk_11' is violated. 1654INSERT INTO t1(c12) VALUES('a'); 1655ERROR HY000: Check constraint 't1_chk_12' is violated. 1656INSERT INTO t1(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12) 1657VALUES('b',"b","b","b","b","b","b","b","b","b","b","b"); 1658DROP TABLE t1; 1659#------------------------------------------------------------------------ 1660# Test case to verify check constraints with date and time data types. 1661#------------------------------------------------------------------------ 1662CREATE TABLE t1 (c1 DATE CHECK(c1 > '2007-01-01'), 1663c2 DATETIME CHECK(c2 > '2007-01-01 12:00:01'), 1664c3 TIMESTAMP CHECK(c3 > '2007-01-01 00:00:01.000000'), 1665c4 TIME CHECK(c4 > '12:00:01.000000'), 1666c5 YEAR CHECK(c5 > '2007')); 1667INSERT INTO t1(c1) VALUES('2006-01-01'); 1668ERROR HY000: Check constraint 't1_chk_1' is violated. 1669INSERT INTO t1(c2) VALUES('2007-01-01 11:00:01'); 1670ERROR HY000: Check constraint 't1_chk_2' is violated. 1671INSERT INTO t1(c3) VALUES('2007-01-01 00:00:00.000000'); 1672ERROR HY000: Check constraint 't1_chk_3' is violated. 1673INSERT INTO t1(c4) VALUES('12:00:00.000000'); 1674ERROR HY000: Check constraint 't1_chk_4' is violated. 1675INSERT INTO t1(c5) VALUES('2006'); 1676ERROR HY000: Check constraint 't1_chk_5' is violated. 1677INSERT INTO t1(c1,c2,c3,c4,c5) 1678VALUES('2008-01-01','2007-01-01 12:00:02','2007-01-01 00:00:02.000000', 1679'12:00:02.000000','2008'); 1680DROP TABLE t1; 1681#------------------------------------------------------------------------ 1682# Test case to verify check constraints with JSON data type. 1683#------------------------------------------------------------------------ 1684CREATE TABLE t1( 1685id INT AUTO_INCREMENT PRIMARY KEY, 1686name VARCHAR(255), 1687browser JSON CHECK( browser->'$.name' = "Chrome" )); 1688INSERT INTO t1(name,browser) 1689VALUES('pageview','{ "name": "Safari", "os": "Mac" }'); 1690ERROR HY000: Check constraint 't1_chk_1' is violated. 1691INSERT INTO t1(name,browser) 1692VALUES('pageview','{ "name": "Chrome", "os": "Mac" }'); 1693SELECT * FROM t1; 1694id name browser 16951 pageview {"os": "Mac", "name": "Chrome"} 1696DROP TABLE t1; 1697#----------------------------------------------------------------------- 1698# check constraint with ENUM data type 1699#----------------------------------------------------------------------- 1700CREATE TABLE t1 (c1 ENUM ('a','b') CHECK (c1 IN ('c', 'd')) ); 1701SHOW CREATE TABLE t1; 1702Table Create Table 1703t1 CREATE TABLE `t1` ( 1704 `c1` enum('a','b') DEFAULT NULL, 1705 CONSTRAINT `t1_chk_1` CHECK ((`c1` in (_utf8mb4'c',_utf8mb4'd'))) 1706) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1707INSERT INTO t1 VALUES('a'); 1708ERROR HY000: Check constraint 't1_chk_1' is violated. 1709INSERT INTO t1 VALUES('b'); 1710ERROR HY000: Check constraint 't1_chk_1' is violated. 1711INSERT INTO t1 VALUES('c'); 1712ERROR 01000: Data truncated for column 'c1' at row 1 1713INSERT INTO t1 VALUES('d'); 1714ERROR 01000: Data truncated for column 'c1' at row 1 1715DROP TABLE t1; 1716#----------------------------------------------------------------------- 1717# check constraint with SET data type 1718#----------------------------------------------------------------------- 1719CREATE TABLE t1 (c1 SET ('a','b') CHECK (c1 IN ('c', 'd')) ); 1720SHOW CREATE TABLE t1; 1721Table Create Table 1722t1 CREATE TABLE `t1` ( 1723 `c1` set('a','b') DEFAULT NULL, 1724 CONSTRAINT `t1_chk_1` CHECK ((`c1` in (_utf8mb4'c',_utf8mb4'd'))) 1725) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1726INSERT INTO t1 VALUES('a'); 1727ERROR HY000: Check constraint 't1_chk_1' is violated. 1728INSERT INTO t1 VALUES('b'); 1729ERROR HY000: Check constraint 't1_chk_1' is violated. 1730INSERT INTO t1 VALUES('c'); 1731ERROR 01000: Data truncated for column 'c1' at row 1 1732INSERT INTO t1 VALUES('d'); 1733ERROR 01000: Data truncated for column 'c1' at row 1 1734DROP TABLE t1; 1735#------------------------------------------------------------------------ 1736# Test case to verify check constraints with spatial data type. 1737#------------------------------------------------------------------------ 1738CREATE TABLE t1( 1739pt POINT CHECK(ST_Equals(pt, ST_GEOMFROMTEXT('POINT(10 20)')) = TRUE), 1740lnstr LINESTRING CHECK(ST_Equals(lnstr, ST_GEOMFROMTEXT('LINESTRING(0 0,5 5,6 6)'))), 1741mlnstr MULTILINESTRING CHECK(ST_Equals(mlnstr, ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 3,4 5), 1742 (6 6,8 8,9 9,10 10))'))), 1743poly POLYGON CHECK(ST_Equals(poly, ST_GEOMFROMTEXT('POLYGON((0 0,6 7,8 8,3 9,0 0), 1744 (3 6,4 6,4 7,3 6))'))), 1745mpoly MULTIPOLYGON CHECK(ST_Equals(mpoly, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)), 1746 ((2 2,4 5,6 2,2 2)))')))); 1747SHOW CREATE TABLE t1; 1748Table Create Table 1749t1 CREATE TABLE `t1` ( 1750 `pt` point DEFAULT NULL, 1751 `lnstr` linestring DEFAULT NULL, 1752 `mlnstr` multilinestring DEFAULT NULL, 1753 `poly` polygon DEFAULT NULL, 1754 `mpoly` multipolygon DEFAULT NULL, 1755 CONSTRAINT `t1_chk_1` CHECK ((st_equals(`pt`,st_geomfromtext(_utf8mb4'POINT(10 20)')) = true)), 1756 CONSTRAINT `t1_chk_2` CHECK (st_equals(`lnstr`,st_geomfromtext(_utf8mb4'LINESTRING(0 0,5 5,6 6)'))), 1757 CONSTRAINT `t1_chk_3` CHECK (st_equals(`mlnstr`,st_geomfromtext(_utf8mb4'MULTILINESTRING((0 0,2 3,4 5),\n (6 6,8 8,9 9,10 10))'))), 1758 CONSTRAINT `t1_chk_4` CHECK (st_equals(`poly`,st_geomfromtext(_utf8mb4'POLYGON((0 0,6 7,8 8,3 9,0 0),\n (3 6,4 6,4 7,3 6))'))), 1759 CONSTRAINT `t1_chk_5` CHECK (st_equals(`mpoly`,st_geomfromtext(_utf8mb4'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),\n ((2 2,4 5,6 2,2 2)))'))) 1760) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1761INSERT INTO t1(pt) VALUES (ST_GEOMFROMTEXT('POINT(10 21)')); 1762ERROR HY000: Check constraint 't1_chk_1' is violated. 1763INSERT INTO t1(lnstr) VALUES (ST_GEOMFROMTEXT('LINESTRING(0 0,5 5,6 7)')); 1764ERROR HY000: Check constraint 't1_chk_2' is violated. 1765INSERT INTO t1(mlnstr) VALUES (ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 3,4 5),(6 6,8 8,9 9,10 11))')); 1766ERROR HY000: Check constraint 't1_chk_3' is violated. 1767INSERT INTO t1(poly) VALUES (ST_GEOMFROMTEXT('POLYGON((0 0,6 7,8 8,3 9,0 0),(3 6,4 6,4 8,3 6))')); 1768ERROR HY000: Check constraint 't1_chk_4' is violated. 1769INSERT INTO t1(mpoly) VALUES (ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((2 2,4 5,6 3,2 2)))')); 1770ERROR HY000: Check constraint 't1_chk_5' is violated. 1771INSERT INTO t1(pt) VALUES (ST_GEOMFROMTEXT('POINT(10 20)')); 1772INSERT INTO t1(lnstr) VALUES (ST_GEOMFROMTEXT('LINESTRING(0 0,5 5,6 6)')); 1773INSERT INTO t1(mlnstr) VALUES (ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 3,4 5),(6 6,8 8,9 9,10 10))')); 1774INSERT INTO t1(poly) VALUES (ST_GEOMFROMTEXT('POLYGON((0 0,6 7,8 8,3 9,0 0),(3 6,4 6,4 7,3 6))')); 1775INSERT INTO t1(mpoly) VALUES (ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((2 2,4 5,6 2,2 2)))')); 1776DROP TABLE t1; 1777#----------------------------------------------------------------------- 1778# Test case to verify check constraint with REGEX 1779#----------------------------------------------------------------------- 1780CREATE TABLE student 1781( 1782id INT, 1783stu_code VARCHAR(10), 1784name VARCHAR(14), 1785email VARCHAR(20), 1786scholarship INT, 1787country VARCHAR(20), 1788CONSTRAINT ck1 CHECK (id != 0), 1789CONSTRAINT ck2 CHECK (stu_code like 'j%'), 1790CONSTRAINT ck3 CHECK (lower(name) != "noname"), 1791CONSTRAINT ck4 CHECK (REGEXP_LIKE(email,'@')), 1792CONSTRAINT ck5 CHECK (scholarship BETWEEN 5000 AND 20000), 1793CONSTRAINT ck6 CHECK (country IN ('usa','uk')) 1794); 1795SHOW CREATE TABLE student; 1796Table Create Table 1797student CREATE TABLE `student` ( 1798 `id` int DEFAULT NULL, 1799 `stu_code` varchar(10) DEFAULT NULL, 1800 `name` varchar(14) DEFAULT NULL, 1801 `email` varchar(20) DEFAULT NULL, 1802 `scholarship` int DEFAULT NULL, 1803 `country` varchar(20) DEFAULT NULL, 1804 CONSTRAINT `ck1` CHECK ((`id` <> 0)), 1805 CONSTRAINT `ck2` CHECK ((`stu_code` like _utf8mb4'j%')), 1806 CONSTRAINT `ck3` CHECK ((lower(`name`) <> _utf8mb4'noname')), 1807 CONSTRAINT `ck4` CHECK (regexp_like(`email`,_utf8mb4'@')), 1808 CONSTRAINT `ck5` CHECK ((`scholarship` between 5000 and 20000)), 1809 CONSTRAINT `ck6` CHECK ((`country` in (_utf8mb4'usa',_utf8mb4'uk'))) 1810) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1811INSERT INTO student VALUES(0,"j001","name1","name1@oracle.com",6000,'usa'); 1812ERROR HY000: Check constraint 'ck1' is violated. 1813INSERT INTO student VALUES(1,"s001","name1","name1@oracle.com",6000,'usa'); 1814ERROR HY000: Check constraint 'ck2' is violated. 1815INSERT INTO student VALUES(1,"j001","NONAME","name1@oracle.com",6000,'usa'); 1816ERROR HY000: Check constraint 'ck3' is violated. 1817INSERT INTO student VALUES(1,"j001","name1","name1oracle.com",6000,'usa'); 1818ERROR HY000: Check constraint 'ck4' is violated. 1819INSERT INTO student VALUES(1,"j001","name1","name1@oracle.com",4000,'usa'); 1820ERROR HY000: Check constraint 'ck5' is violated. 1821INSERT INTO student VALUES(1,"j001","name1","name1@oracle.com",6000,'nocountry'); 1822ERROR HY000: Check constraint 'ck6' is violated. 1823INSERT INTO student VALUES(1,"j001","name1","name1@oracle.com",6000,'usa'); 1824SELECT * FROM student; 1825id stu_code name email scholarship country 18261 j001 name1 name1@oracle.com 6000 usa 1827DROP TABLE student; 1828#----------------------------------------------------------------------- 1829# Test case to verify check constraint with numeric comparator 1830# operators with varchar columns. 1831#----------------------------------------------------------------------- 1832CREATE TABLE t1(c1 INT, c2 VARCHAR(20)); 1833ALTER TABLE t1 ADD CONSTRAINT ck1 CHECK ( c1 > c2 ); 1834SHOW CREATE TABLE t1; 1835Table Create Table 1836t1 CREATE TABLE `t1` ( 1837 `c1` int DEFAULT NULL, 1838 `c2` varchar(20) DEFAULT NULL, 1839 CONSTRAINT `ck1` CHECK ((`c1` > `c2`)) 1840) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1841DROP TABLE t1; 1842#----------------------------------------------------------------------- 1843# Test case to verify check constraint with Comparison Functions 1844# and Operators 1845#----------------------------------------------------------------------- 1846CREATE TABLE t1(c1 INT, CHECK ( c1 IN ( SELECT COALESCE(NULL, 1, 1)))); 1847ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function. 1848CREATE TABLE t1(c1 INT, CHECK ( c1 < ( SELECT COALESCE(NULL, 1, 1)))); 1849ERROR HY000: An expression of a check constraint 't1_chk_1' contains disallowed function. 1850CREATE TABLE t1(c1 INT , CHECK ( c1 <=> NULL )); 1851INSERT INTO t1 VALUES(1); 1852ERROR HY000: Check constraint 't1_chk_1' is violated. 1853INSERT INTO t1 VALUES(NULL); 1854SELECT * FROM t1; 1855c1 1856NULL 1857ALTER TABLE t1 ADD COLUMN c2 INT, ADD CONSTRAINT CHECK( c2 > 10 ); 1858INSERT INTO t1(c2) VALUES(10); 1859ERROR HY000: Check constraint 't1_chk_2' is violated. 1860INSERT INTO t1(c2) VALUES(11); 1861ALTER TABLE t1 ADD COLUMN c3 INT, ADD CONSTRAINT CHECK( c3 >= 10 ); 1862INSERT INTO t1(c3) VALUES(9); 1863ERROR HY000: Check constraint 't1_chk_3' is violated. 1864INSERT INTO t1(c3) VALUES(10); 1865ALTER TABLE t1 ADD COLUMN c4 INT, ADD CONSTRAINT CHECK( c4 < 10 ); 1866INSERT INTO t1(c4) VALUES(10); 1867ERROR HY000: Check constraint 't1_chk_4' is violated. 1868INSERT INTO t1(c4) VALUES(9); 1869ALTER TABLE t1 ADD COLUMN c5 INT, ADD CONSTRAINT CHECK( c5 <= 10 ); 1870INSERT INTO t1(c5) VALUES(11); 1871ERROR HY000: Check constraint 't1_chk_5' is violated. 1872INSERT INTO t1(c5) VALUES(10); 1873ALTER TABLE t1 ADD COLUMN c6 INT, ADD CONSTRAINT CHECK( c6 != 10 ); 1874INSERT INTO t1(c6) VALUES(10); 1875ERROR HY000: Check constraint 't1_chk_6' is violated. 1876INSERT INTO t1(c6) VALUES(20); 1877ALTER TABLE t1 ADD COLUMN c7 INT, ADD CONSTRAINT CHECK( c7 <> 10 ); 1878INSERT INTO t1(c7) VALUES(10); 1879ERROR HY000: Check constraint 't1_chk_7' is violated. 1880INSERT INTO t1(c7) VALUES(20); 1881ALTER TABLE t1 ADD COLUMN c8 INT, ADD CONSTRAINT CHECK( c8 = GREATEST(1,2,3) ); 1882INSERT INTO t1(c8) VALUES(1); 1883ERROR HY000: Check constraint 't1_chk_8' is violated. 1884INSERT INTO t1(c8) VALUES(3); 1885ALTER TABLE t1 ADD COLUMN c9 INT, ADD CONSTRAINT CHECK( c9 = LEAST(1,2,3) ); 1886INSERT INTO t1(c9) VALUES(3); 1887ERROR HY000: Check constraint 't1_chk_9' is violated. 1888INSERT INTO t1(c9) VALUES(1); 1889ALTER TABLE t1 ADD COLUMN c10 INT, ADD CONSTRAINT CHECK( c10 NOT IN (1,2,3) ); 1890INSERT INTO t1(c10) VALUES(1); 1891ERROR HY000: Check constraint 't1_chk_10' is violated. 1892INSERT INTO t1(c10) VALUES(3); 1893ERROR HY000: Check constraint 't1_chk_10' is violated. 1894INSERT INTO t1(c10) VALUES(10); 1895ALTER TABLE t1 ADD COLUMN c11 YEAR, ADD CONSTRAINT CHECK ( c11 > '2007-01-01' + INTERVAL +1 YEAR); 1896INSERT INTO t1(c11) VALUES(2007); 1897ERROR HY000: Check constraint 't1_chk_11' is violated. 1898INSERT INTO t1(c11) VALUES(2008); 1899ERROR HY000: Check constraint 't1_chk_11' is violated. 1900INSERT INTO t1(c11) VALUES(2009); 1901ALTER TABLE t1 ADD COLUMN c12 INT, ADD CONSTRAINT CHECK ( c12 NOT BETWEEN 10 AND 20); 1902INSERT INTO t1(c12) VALUES(15); 1903ERROR HY000: Check constraint 't1_chk_12' is violated. 1904INSERT INTO t1(c12) VALUES(25); 1905ALTER TABLE t1 ADD COLUMN c13 INT, ADD CONSTRAINT CHECK ( c13 NOT IN (1, 2, 3)); 1906INSERT INTO t1(c13) VALUES(1); 1907ERROR HY000: Check constraint 't1_chk_13' is violated. 1908INSERT INTO t1(c13) VALUES(15); 1909ALTER TABLE t1 ADD COLUMN c14 CHAR(10), ADD CONSTRAINT CHECK ( c14 LIKE 'A%'); 1910INSERT INTO t1(c14) VALUES('Good'); 1911ERROR HY000: Check constraint 't1_chk_14' is violated. 1912INSERT INTO t1(c14) VALUES('All'); 1913ALTER TABLE t1 ADD COLUMN c15 INT, ADD CONSTRAINT CHECK ( c15 = STRCMP('A','A')); 1914INSERT INTO t1(c15) VALUES(1); 1915ERROR HY000: Check constraint 't1_chk_15' is violated. 1916INSERT INTO t1(c15) VALUES(0); 1917SHOW CREATE TABLE t1; 1918Table Create Table 1919t1 CREATE TABLE `t1` ( 1920 `c1` int DEFAULT NULL, 1921 `c2` int DEFAULT NULL, 1922 `c3` int DEFAULT NULL, 1923 `c4` int DEFAULT NULL, 1924 `c5` int DEFAULT NULL, 1925 `c6` int DEFAULT NULL, 1926 `c7` int DEFAULT NULL, 1927 `c8` int DEFAULT NULL, 1928 `c9` int DEFAULT NULL, 1929 `c10` int DEFAULT NULL, 1930 `c11` year DEFAULT NULL, 1931 `c12` int DEFAULT NULL, 1932 `c13` int DEFAULT NULL, 1933 `c14` char(10) DEFAULT NULL, 1934 `c15` int DEFAULT NULL, 1935 CONSTRAINT `t1_chk_1` CHECK ((`c1` <=> NULL)), 1936 CONSTRAINT `t1_chk_10` CHECK ((`c10` not in (1,2,3))), 1937 CONSTRAINT `t1_chk_11` CHECK ((`c11` > 2008)), 1938 CONSTRAINT `t1_chk_12` CHECK ((`c12` not between 10 and 20)), 1939 CONSTRAINT `t1_chk_13` CHECK ((`c13` not in (1,2,3))), 1940 CONSTRAINT `t1_chk_14` CHECK ((`c14` like _utf8mb4'A%')), 1941 CONSTRAINT `t1_chk_15` CHECK ((`c15` = strcmp(_utf8mb4'A',_utf8mb4'A'))), 1942 CONSTRAINT `t1_chk_2` CHECK ((`c2` > 10)), 1943 CONSTRAINT `t1_chk_3` CHECK ((`c3` >= 10)), 1944 CONSTRAINT `t1_chk_4` CHECK ((`c4` < 10)), 1945 CONSTRAINT `t1_chk_5` CHECK ((`c5` <= 10)), 1946 CONSTRAINT `t1_chk_6` CHECK ((`c6` <> 10)), 1947 CONSTRAINT `t1_chk_7` CHECK ((`c7` <> 10)), 1948 CONSTRAINT `t1_chk_8` CHECK ((`c8` = greatest(1,2,3))), 1949 CONSTRAINT `t1_chk_9` CHECK ((`c9` = least(1,2,3))) 1950) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1951DROP TABLE t1; 1952#----------------------------------------------------------------------- 1953# Test case to verify check constraint with Logical Operators 1954#----------------------------------------------------------------------- 1955CREATE TABLE t1(c1 INT, c2 INT); 1956ALTER TABLE t1 ADD CONSTRAINT CHECK( (c1 > 10) AND (c2 < 20) ); 1957INSERT INTO t1 VALUES(1,10); 1958ERROR HY000: Check constraint 't1_chk_1' is violated. 1959ALTER TABLE t1 ADD CONSTRAINT CHECK( (c1 > 10) && (c2 < 20) ); 1960Warnings: 1961Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead 1962INSERT INTO t1 VALUES(15,25); 1963ERROR HY000: Check constraint 't1_chk_1' is violated. 1964ALTER TABLE t1 DROP CHECK `t1_chk_1`; 1965ALTER TABLE t1 DROP CHECK `t1_chk_2`; 1966ALTER TABLE t1 ADD CONSTRAINT CHECK( (c1 > 10) || (c2 < 20) ); 1967Warnings: 1968Warning 1287 '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead 1969ALTER TABLE t1 ADD CONSTRAINT CHECK( (c1 > 10) OR (c2 < 20) ); 1970INSERT INTO t1 VALUES(15,25); 1971INSERT INTO t1 VALUES(5,10); 1972INSERT INTO t1 VALUES(5,25); 1973ERROR HY000: Check constraint 't1_chk_1' is violated. 1974ALTER TABLE t1 DROP CHECK `t1_chk_1`; 1975ALTER TABLE t1 DROP CHECK `t1_chk_2`; 1976DELETE FROM t1; 1977ALTER TABLE t1 ADD CONSTRAINT CHECK( (c1 > 10) XOR (c2 < 20) ); 1978INSERT INTO t1 VALUES(15,10); 1979ERROR HY000: Check constraint 't1_chk_1' is violated. 1980SHOW CREATE TABLE t1; 1981Table Create Table 1982t1 CREATE TABLE `t1` ( 1983 `c1` int DEFAULT NULL, 1984 `c2` int DEFAULT NULL, 1985 CONSTRAINT `t1_chk_1` CHECK (((`c1` > 10) xor (`c2` < 20))) 1986) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1987DROP TABLE t1; 1988#----------------------------------------------------------------------- 1989# Test case to verify check constraint behaviour with DEFAULT, NULL 1990# and with LOGICAL operators. 1991#----------------------------------------------------------------------- 1992CREATE TABLE t1(c1 INT DEFAULT 2 PRIMARY KEY CHECK(c1 > 1 OR c1 IS NOT NULL)); 1993SHOW CREATE TABLE t1; 1994Table Create Table 1995t1 CREATE TABLE `t1` ( 1996 `c1` int NOT NULL DEFAULT '2', 1997 PRIMARY KEY (`c1`), 1998 CONSTRAINT `t1_chk_1` CHECK (((`c1` > 1) or (`c1` is not null))) 1999) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2000INSERT INTO t1 VALUES(NULL); 2001ERROR 23000: Column 'c1' cannot be null 2002INSERT INTO t1 VALUES(1); 2003SELECT * FROM t1; 2004c1 20051 2006DROP TABLE t1; 2007CREATE TABLE t1(c1 INT DEFAULT 2 PRIMARY KEY CHECK(c1 > 1 OR c1 > 2)); 2008INSERT INTO t1 VALUES(1); 2009ERROR HY000: Check constraint 't1_chk_1' is violated. 2010INSERT INTO t1 VALUES(2); 2011SELECT * FROM t1; 2012c1 20132 2014DROP TABLE t1; 2015CREATE TABLE t1(c1 INT DEFAULT 2 PRIMARY KEY CHECK(c1 > 1 AND c1 IS NOT NULL)); 2016INSERT INTO t1 VALUES(1); 2017ERROR HY000: Check constraint 't1_chk_1' is violated. 2018DROP TABLE t1; 2019#----------------------------------------------------------------------- 2020# Test case to verify check constraint when table is moved to another DB 2021#----------------------------------------------------------------------- 2022CREATE DATABASE test1; 2023CREATE DATABASE test2; 2024USE test1; 2025CREATE TABLE t1(c1 INT, c2 INT CHECK (c2 < 10)); 2026INSERT INTO t1 VALUES(1,1); 2027SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 2028CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 2029def test1 t1_chk_1 (`c2` < 10) 2030SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1'; 2031CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED 2032def test1 t1_chk_1 test1 t1 CHECK YES 2033ALTER TABLE test1.t1 rename test2.t1; 2034USE test2; 2035SELECT * FROM t1; 2036c1 c2 20371 1 2038SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 2039CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 2040def test2 t1_chk_1 (`c2` < 10) 2041SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1'; 2042CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED 2043def test2 t1_chk_1 test2 t1 CHECK YES 2044DROP DATABASE test2; 2045DROP DATABASE test1; 2046#----------------------------------------------------------------------- 2047# Test case to verify check constraint when table is moved to another DB 2048# with different name. 2049#----------------------------------------------------------------------- 2050CREATE DATABASE test1; 2051CREATE DATABASE test2; 2052USE test1; 2053CREATE TABLE t1(c1 INT, c2 INT CHECK (c2 < 10)); 2054INSERT INTO t1 VALUES(1,1); 2055SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 2056CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 2057def test1 t1_chk_1 (`c2` < 10) 2058SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t1'; 2059CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED 2060def test1 t1_chk_1 test1 t1 CHECK YES 2061ALTER TABLE test1.t1 rename test2.t2; 2062USE test2; 2063SELECT * FROM t2; 2064c1 c2 20651 1 2066SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS; 2067CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME CHECK_CLAUSE 2068def test2 t2_chk_1 (`c2` < 10) 2069SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='t2'; 2070CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED 2071def test2 t2_chk_1 test2 t2 CHECK YES 2072DROP DATABASE test2; 2073DROP DATABASE test1; 2074use test; 2075#----------------------------------------------------------------------- 2076# Test case to verify check constraints with foreign key constraint 2077#----------------------------------------------------------------------- 2078CREATE TABLE parent(pid INT NOT NULL PRIMARY KEY CHECK(pid > 1)); 2079CREATE TABLE child(cid INT CHECK(cid > 1), 2080CONSTRAINT fk FOREIGN KEY (cid) REFERENCES parent(pid)); 2081SHOW CREATE TABLE parent; 2082Table Create Table 2083parent CREATE TABLE `parent` ( 2084 `pid` int NOT NULL, 2085 PRIMARY KEY (`pid`), 2086 CONSTRAINT `parent_chk_1` CHECK ((`pid` > 1)) 2087) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2088SHOW CREATE TABLE child; 2089Table Create Table 2090child CREATE TABLE `child` ( 2091 `cid` int DEFAULT NULL, 2092 KEY `fk` (`cid`), 2093 CONSTRAINT `fk` FOREIGN KEY (`cid`) REFERENCES `parent` (`pid`), 2094 CONSTRAINT `child_chk_1` CHECK ((`cid` > 1)) 2095) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2096INSERT INTO parent VALUES(2); 2097INSERT INTO child VALUES(1); 2098ERROR HY000: Check constraint 'child_chk_1' is violated. 2099INSERT INTO child VALUES(3); 2100ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk` FOREIGN KEY (`cid`) REFERENCES `parent` (`pid`)) 2101INSERT INTO child VALUES(2); 2102SELECT * FROM parent; 2103pid 21042 2105SELECT * FROM child; 2106cid 21072 2108DROP TABLE child; 2109DROP TABLE parent; 2110#----------------------------------------------------------------------- 2111# Test case to verify check constraint with FK referential actions. 2112#----------------------------------------------------------------------- 2113CREATE TABLE parent (a INT PRIMARY KEY); 2114CREATE TABLE child ( 2115b INT, 2116c INT CHECK (c < 10), 2117INDEX(b), 2118FOREIGN KEY (b) REFERENCES parent(a) ON DELETE SET NULL, 2119CHECK (b IS NOT NULL) 2120); 2121ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2122CREATE TABLE child ( 2123b INT, 2124c INT CHECK (c < 10), 2125INDEX(b), 2126FOREIGN KEY (b) REFERENCES parent(a) ON DELETE SET NULL 2127); 2128ALTER TABLE child ADD CONSTRAINT CHECK (b IS NOT NULL); 2129ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2130ALTER TABLE child DROP FOREIGN KEY child_ibfk_1; 2131ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (b) REFERENCES parent(a) ON DELETE SET NULL, 2132ADD CONSTRAINT CHECK (b IS NOT NULL); 2133ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2134DROP TABLE child; 2135CREATE TABLE child ( 2136b INT, 2137c INT CHECK (c < 10), 2138INDEX(b), 2139FOREIGN KEY (b) REFERENCES parent(a) ON UPDATE SET NULL, 2140CHECK (b IS NOT NULL) 2141); 2142ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2143CREATE TABLE child ( 2144b INT, 2145c INT CHECK (c < 10), 2146INDEX(b), 2147FOREIGN KEY (b) REFERENCES parent(a) ON UPDATE SET NULL 2148); 2149ALTER TABLE child ADD CONSTRAINT CHECK (b IS NOT NULL); 2150ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2151ALTER TABLE child DROP FOREIGN KEY child_ibfk_1; 2152ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (b) REFERENCES parent(a) ON UPDATE SET NULL, 2153ADD CONSTRAINT CHECK (b IS NOT NULL); 2154ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2155DROP TABLE child; 2156CREATE TABLE child ( 2157b INT, 2158c INT CHECK (c < 10), 2159INDEX(b), 2160FOREIGN KEY (b) REFERENCES parent(a) ON UPDATE CASCADE, 2161CHECK (b IS NOT NULL) 2162); 2163ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2164CREATE TABLE child ( 2165b INT, 2166c INT CHECK (c < 10), 2167INDEX(b), 2168FOREIGN KEY (b) REFERENCES parent(a) ON UPDATE CASCADE 2169); 2170ALTER TABLE child ADD CONSTRAINT CHECK (b IS NOT NULL); 2171ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2172ALTER TABLE child DROP FOREIGN KEY child_ibfk_1; 2173ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (b) REFERENCES parent(a) ON UPDATE CASCADE, 2174ADD CONSTRAINT CHECK (b IS NOT NULL); 2175ERROR HY000: Column 'b' cannot be used in a check constraint 'child_chk_2': needed in a foreign key constraint 'child_ibfk_1' referential action. 2176DROP TABLE child; 2177DROP TABLE parent; 2178#----------------------------------------------------------------------- 2179# Test case to verify check constraint with triggers 2180#----------------------------------------------------------------------- 2181CREATE TABLE t1(c1 INT, c2 INT CHECK (c2 < 10)); 2182CREATE TABLE t2(c1 INT, c2 INT); 2183CREATE TRIGGER before_t2_insert 2184BEFORE INSERT ON t2 2185FOR EACH ROW 2186BEGIN 2187INSERT INTO t1 VALUES(NEW.c1,NEW.c2); 2188END 2189// 2190INSERT INTO t2 VALUES(1,20); 2191ERROR HY000: Check constraint 't1_chk_1' is violated. 2192SELECT * FROM t1; 2193c1 c2 2194SELECT * FROM t2; 2195c1 c2 2196DROP TRIGGER before_t2_insert; 2197CREATE TRIGGER after_t2_insert 2198AFTER INSERT ON t2 2199FOR EACH ROW 2200BEGIN 2201INSERT INTO t1 VALUES(NEW.c1,NEW.c2); 2202END 2203// 2204INSERT INTO t2 VALUES(1,30); 2205ERROR HY000: Check constraint 't1_chk_1' is violated. 2206SELECT * FROM t1; 2207c1 c2 2208SELECT * FROM t2; 2209c1 c2 2210DROP TRIGGER after_t2_insert; 2211INSERT INTO t2 VALUES(1,5); 2212INSERT INTO t1 VALUES(1,5); 2213CREATE TRIGGER before_t2_update 2214BEFORE UPDATE ON t2 2215FOR EACH ROW 2216BEGIN 2217UPDATE t1 SET c2=NEW.c2 WHERE c1=NEW.c1; 2218END 2219// 2220UPDATE t2 SET c2=20 WHERE c1=1; 2221ERROR HY000: Check constraint 't1_chk_1' is violated. 2222SELECT * FROM t1; 2223c1 c2 22241 5 2225SELECT * FROM t2; 2226c1 c2 22271 5 2228DROP TRIGGER before_t2_update; 2229CREATE TRIGGER after_t2_update 2230AFTER UPDATE ON t2 2231FOR EACH ROW 2232BEGIN 2233UPDATE t1 SET c2=NEW.c2 WHERE c1=NEW.c1; 2234END 2235// 2236UPDATE t2 SET c2=20 WHERE c1=1; 2237ERROR HY000: Check constraint 't1_chk_1' is violated. 2238SELECT * FROM t1; 2239c1 c2 22401 5 2241SELECT * FROM t2; 2242c1 c2 22431 5 2244DROP TRIGGER after_t2_update; 2245CREATE TRIGGER before_t1_insert 2246BEFORE INSERT ON t1 2247FOR EACH ROW 2248BEGIN 2249IF (NEW.c2 >= 10) THEN 2250SET NEW.c2 = 0; 2251END IF; 2252END 2253// 2254CREATE TRIGGER before_t1_update 2255BEFORE UPDATE ON t1 2256FOR EACH ROW 2257BEGIN 2258IF (NEW.c2 >= 10) THEN 2259SET NEW.c2 = 0; 2260END IF; 2261END 2262// 2263INSERT INTO t1 VALUES(1, 11); 2264UPDATE t1 SET c2 = 11 WHERE c1 = 1; 2265DROP TRIGGER before_t1_insert; 2266DROP TRIGGER before_t1_update; 2267DROP TABLE t1,t2; 2268#----------------------------------------------------------------------- 2269# Test case uses triggers to work as check constraints. 2270#----------------------------------------------------------------------- 2271CREATE TABLE t1(c1 int CONSTRAINT ck1 CHECK(c1 < 5)); 2272CREATE PROCEDURE proc1 (IN val1 INT) 2273BEGIN 2274IF val1 < 10 THEN 2275SIGNAL SQLSTATE '45000' 2276 SET MESSAGE_TEXT = 'check constraint on c1 failed'; 2277END IF; 2278END 2279// 2280CREATE TRIGGER before_t1_insert 2281BEFORE INSERT ON t1 2282FOR EACH ROW 2283BEGIN 2284CALL proc1(new.c1); 2285END 2286// 2287INSERT INTO t1 VALUES(20); 2288ERROR HY000: Check constraint 'ck1' is violated. 2289INSERT INTO t1 VALUES(9); 2290ERROR 45000: check constraint on c1 failed 2291INSERT INTO t1 VALUES(4); 2292ERROR 45000: check constraint on c1 failed 2293DROP PROCEDURE proc1; 2294DROP TRIGGER before_t1_insert; 2295DROP TABLE t1; 2296#----------------------------------------------------------------------- 2297# Test case to verify check constraint with mysqldump 2298#----------------------------------------------------------------------- 2299CREATE DATABASE test1; 2300USE test1; 2301CREATE TABLE t1 ( 2302c1 BIT(7) CHECK(c1 < B'1111100') NOT ENFORCED, 2303c2 BOOLEAN CHECK(c2 > 0) NOT ENFORCED, 2304c3 TINYINT CHECK(c3 > 10) NOT ENFORCED, 2305c4 SMALLINT CHECK(c4 > 10) NOT ENFORCED, 2306c5 MEDIUMINT CHECK(c5 > 10) NOT ENFORCED, 2307c6 INT CHECK(c6 > 10), 2308c7 BIGINT CHECK(c7 > 10), 2309c8 DECIMAL(6,2) CHECK(c8 > 10.1), 2310c9 FLOAT(6,2) CHECK(c9 > 10.1), 2311c10 DOUBLE(6,2) CHECK(c10 > 10.1), 2312c11 CHAR(6) CHECK (c11 IS NULL)); 2313Warnings: 2314Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. 2315Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. 2316SHOW CREATE TABLE t1; 2317Table Create Table 2318t1 CREATE TABLE `t1` ( 2319 `c1` bit(7) DEFAULT NULL, 2320 `c2` tinyint(1) DEFAULT NULL, 2321 `c3` tinyint DEFAULT NULL, 2322 `c4` smallint DEFAULT NULL, 2323 `c5` mediumint DEFAULT NULL, 2324 `c6` int DEFAULT NULL, 2325 `c7` bigint DEFAULT NULL, 2326 `c8` decimal(6,2) DEFAULT NULL, 2327 `c9` float(6,2) DEFAULT NULL, 2328 `c10` double(6,2) DEFAULT NULL, 2329 `c11` char(6) DEFAULT NULL, 2330 CONSTRAINT `t1_chk_1` CHECK ((`c1` < 0x7c)) /*!80016 NOT ENFORCED */, 2331 CONSTRAINT `t1_chk_10` CHECK ((`c10` > 10.1)), 2332 CONSTRAINT `t1_chk_11` CHECK ((`c11` is null)), 2333 CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)) /*!80016 NOT ENFORCED */, 2334 CONSTRAINT `t1_chk_3` CHECK ((`c3` > 10)) /*!80016 NOT ENFORCED */, 2335 CONSTRAINT `t1_chk_4` CHECK ((`c4` > 10)) /*!80016 NOT ENFORCED */, 2336 CONSTRAINT `t1_chk_5` CHECK ((`c5` > 10)) /*!80016 NOT ENFORCED */, 2337 CONSTRAINT `t1_chk_6` CHECK ((`c6` > 10)), 2338 CONSTRAINT `t1_chk_7` CHECK ((`c7` > 10)), 2339 CONSTRAINT `t1_chk_8` CHECK ((`c8` > 10.1)), 2340 CONSTRAINT `t1_chk_9` CHECK ((`c9` > 10.1)) 2341) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2342DROP DATABASE test1; 2343USE test1; 2344SHOW CREATE TABLE t1; 2345Table Create Table 2346t1 CREATE TABLE `t1` ( 2347 `c1` bit(7) DEFAULT NULL, 2348 `c2` tinyint(1) DEFAULT NULL, 2349 `c3` tinyint DEFAULT NULL, 2350 `c4` smallint DEFAULT NULL, 2351 `c5` mediumint DEFAULT NULL, 2352 `c6` int DEFAULT NULL, 2353 `c7` bigint DEFAULT NULL, 2354 `c8` decimal(6,2) DEFAULT NULL, 2355 `c9` float(6,2) DEFAULT NULL, 2356 `c10` double(6,2) DEFAULT NULL, 2357 `c11` char(6) DEFAULT NULL, 2358 CONSTRAINT `t1_chk_1` CHECK ((`c1` < 0x7c)) /*!80016 NOT ENFORCED */, 2359 CONSTRAINT `t1_chk_10` CHECK ((`c10` > 10.1)), 2360 CONSTRAINT `t1_chk_11` CHECK ((`c11` is null)), 2361 CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)) /*!80016 NOT ENFORCED */, 2362 CONSTRAINT `t1_chk_3` CHECK ((`c3` > 10)) /*!80016 NOT ENFORCED */, 2363 CONSTRAINT `t1_chk_4` CHECK ((`c4` > 10)) /*!80016 NOT ENFORCED */, 2364 CONSTRAINT `t1_chk_5` CHECK ((`c5` > 10)) /*!80016 NOT ENFORCED */, 2365 CONSTRAINT `t1_chk_6` CHECK ((`c6` > 10)), 2366 CONSTRAINT `t1_chk_7` CHECK ((`c7` > 10)), 2367 CONSTRAINT `t1_chk_8` CHECK ((`c8` > 10.1)), 2368 CONSTRAINT `t1_chk_9` CHECK ((`c9` > 10.1)) 2369) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2370INSERT INTO t1(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) 2371VALUES(B'1111111',0,5,5,5,1,1,1.2,1.2,1.2); 2372ERROR HY000: Check constraint 't1_chk_10' is violated. 2373INSERT INTO t1(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) 2374VALUES(B'1111111',0,5,5,5,11,11,10.2,10.2,10.2); 2375SELECT * FROM t1; 2376c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 2377 0 5 5 5 11 11 10.20 10.20 10.20 NULL 2378DROP TABLE t1; 2379DROP DATABASE test1; 2380#----------------------------------------------------------------------- 2381# Test case to verify check constraint with mysqlpump 2382#----------------------------------------------------------------------- 2383CREATE DATABASE test2; 2384USE test2; 2385CREATE TABLE t2 ( 2386c1 BIT(7) CHECK(c1 < B'1111100'), 2387c2 BOOLEAN CHECK(c2 > 0), 2388c3 TINYINT CHECK(c3 > 10), 2389c4 SMALLINT CHECK(c4 > 10), 2390c5 MEDIUMINT CHECK(c5 > 10), 2391c6 INT CHECK(c6 > 10) NOT ENFORCED, 2392c7 BIGINT CHECK(c7 > 10) NOT ENFORCED, 2393c8 DECIMAL(6,2) CHECK(c8 > 10.1) NOT ENFORCED, 2394c9 FLOAT(6,2) CHECK(c9 > 10.1) NOT ENFORCED, 2395c10 DOUBLE(6,2) CHECK(c10 > 10.1) NOT ENFORCED); 2396Warnings: 2397Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. 2398Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. 2399SHOW CREATE TABLE t2; 2400Table Create Table 2401t2 CREATE TABLE `t2` ( 2402 `c1` bit(7) DEFAULT NULL, 2403 `c2` tinyint(1) DEFAULT NULL, 2404 `c3` tinyint DEFAULT NULL, 2405 `c4` smallint DEFAULT NULL, 2406 `c5` mediumint DEFAULT NULL, 2407 `c6` int DEFAULT NULL, 2408 `c7` bigint DEFAULT NULL, 2409 `c8` decimal(6,2) DEFAULT NULL, 2410 `c9` float(6,2) DEFAULT NULL, 2411 `c10` double(6,2) DEFAULT NULL, 2412 CONSTRAINT `t2_chk_1` CHECK ((`c1` < 0x7c)), 2413 CONSTRAINT `t2_chk_10` CHECK ((`c10` > 10.1)) /*!80016 NOT ENFORCED */, 2414 CONSTRAINT `t2_chk_2` CHECK ((`c2` > 0)), 2415 CONSTRAINT `t2_chk_3` CHECK ((`c3` > 10)), 2416 CONSTRAINT `t2_chk_4` CHECK ((`c4` > 10)), 2417 CONSTRAINT `t2_chk_5` CHECK ((`c5` > 10)), 2418 CONSTRAINT `t2_chk_6` CHECK ((`c6` > 10)) /*!80016 NOT ENFORCED */, 2419 CONSTRAINT `t2_chk_7` CHECK ((`c7` > 10)) /*!80016 NOT ENFORCED */, 2420 CONSTRAINT `t2_chk_8` CHECK ((`c8` > 10.1)) /*!80016 NOT ENFORCED */, 2421 CONSTRAINT `t2_chk_9` CHECK ((`c9` > 10.1)) /*!80016 NOT ENFORCED */ 2422) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2423DROP DATABASE test2; 2424USE test2; 2425SHOW CREATE TABLE t2; 2426Table Create Table 2427t2 CREATE TABLE `t2` ( 2428 `c1` bit(7) DEFAULT NULL, 2429 `c2` tinyint(1) DEFAULT NULL, 2430 `c3` tinyint DEFAULT NULL, 2431 `c4` smallint DEFAULT NULL, 2432 `c5` mediumint DEFAULT NULL, 2433 `c6` int DEFAULT NULL, 2434 `c7` bigint DEFAULT NULL, 2435 `c8` decimal(6,2) DEFAULT NULL, 2436 `c9` float(6,2) DEFAULT NULL, 2437 `c10` double(6,2) DEFAULT NULL, 2438 CONSTRAINT `t2_chk_1` CHECK ((`c1` < 0x7c)), 2439 CONSTRAINT `t2_chk_10` CHECK ((`c10` > 10.1)) /*!80016 NOT ENFORCED */, 2440 CONSTRAINT `t2_chk_2` CHECK ((`c2` > 0)), 2441 CONSTRAINT `t2_chk_3` CHECK ((`c3` > 10)), 2442 CONSTRAINT `t2_chk_4` CHECK ((`c4` > 10)), 2443 CONSTRAINT `t2_chk_5` CHECK ((`c5` > 10)), 2444 CONSTRAINT `t2_chk_6` CHECK ((`c6` > 10)) /*!80016 NOT ENFORCED */, 2445 CONSTRAINT `t2_chk_7` CHECK ((`c7` > 10)) /*!80016 NOT ENFORCED */, 2446 CONSTRAINT `t2_chk_8` CHECK ((`c8` > 10.1)) /*!80016 NOT ENFORCED */, 2447 CONSTRAINT `t2_chk_9` CHECK ((`c9` > 10.1)) /*!80016 NOT ENFORCED */ 2448) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2449INSERT INTO t2(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) 2450VALUES(B'1111000',0,10,10,10,5,5,9.1,9.1,9.1); 2451ERROR HY000: Check constraint 't2_chk_2' is violated. 2452INSERT INTO t2(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10) 2453VALUES(B'1111000',1,11,11,11,5,5,9.1,9.1,9.1); 2454SELECT * FROM t2; 2455c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 2456x 1 11 11 11 5 5 9.10 9.10 9.10 2457DROP TABLE t2; 2458DROP DATABASE test2; 2459USE test; 2460#----------------------------------------------------------------------- 2461# Test case to verify check constraint with PREPARE statement 2462#----------------------------------------------------------------------- 2463CREATE TABLE t1(c1 INT CHECK(c1 > 10)); 2464PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(1)'; 2465EXECUTE stmt1; 2466ERROR HY000: Check constraint 't1_chk_1' is violated. 2467DEALLOCATE PREPARE stmt1; 2468PREPARE stmt2 FROM 'INSERT INTO t1 VALUES(20)'; 2469EXECUTE stmt2; 2470DEALLOCATE PREPARE stmt2; 2471SELECT * FROM t1; 2472c1 247320 2474DROP TABLE t1; 2475#----------------------------------------------------------------------- 2476# Test case to verify check constraint behaviour inside transaction 2477#----------------------------------------------------------------------- 2478CREATE TABLE t1(c1 INT); 2479CREATE TABLE t2(c1 INT CHECK(c1 > 10)); 2480SET AUTOCOMMIT = OFF; 2481START TRANSACTION; 2482INSERT INTO t1 VALUES(1); 2483INSERT INTO t2 VALUES(1); 2484ERROR HY000: Check constraint 't2_chk_1' is violated. 2485ROLLBACK; 2486SELECT * FROM t1; 2487c1 2488SELECT * FROM t2; 2489c1 2490START TRANSACTION; 2491ALTER TABLE t1 ADD CONSTRAINT CHECK (C1 > 10); 2492COMMIT; 2493SHOW CREATE TABLE t1; 2494Table Create Table 2495t1 CREATE TABLE `t1` ( 2496 `c1` int DEFAULT NULL, 2497 CONSTRAINT `t1_chk_1` CHECK ((`C1` > 10)) 2498) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2499SET AUTOCOMMIT = ON; 2500DROP TABLE t1,t2; 2501#------------------------------------------------------------------------ 2502# Test case to verify check constraints with Partition table. 2503#------------------------------------------------------------------------ 2504# check constraint with range partition 2505CREATE TABLE t1( 2506d DATE NOT NULL CHECK(YEAR(d) > '1950') 2507) 2508PARTITION BY RANGE( YEAR(d) ) ( 2509PARTITION p0 VALUES LESS THAN (1960), 2510PARTITION p1 VALUES LESS THAN (1970), 2511PARTITION p2 VALUES LESS THAN (1980), 2512PARTITION p3 VALUES LESS THAN (1990) 2513); 2514SHOW CREATE TABLE t1; 2515Table Create Table 2516t1 CREATE TABLE `t1` ( 2517 `d` date NOT NULL, 2518 CONSTRAINT `t1_chk_1` CHECK ((year(`d`) > _utf8mb4'1950')) 2519) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2520/*!50100 PARTITION BY RANGE (year(`d`)) 2521(PARTITION p0 VALUES LESS THAN (1960) ENGINE = InnoDB, 2522 PARTITION p1 VALUES LESS THAN (1970) ENGINE = InnoDB, 2523 PARTITION p2 VALUES LESS THAN (1980) ENGINE = InnoDB, 2524 PARTITION p3 VALUES LESS THAN (1990) ENGINE = InnoDB) */ 2525INSERT INTO t1 VALUES('1940-01-01'); 2526ERROR HY000: Check constraint 't1_chk_1' is violated. 2527INSERT INTO t1 VALUES('1960-01-01'); 2528SELECT * FROM t1; 2529d 25301960-01-01 2531DROP TABLE t1; 2532# check constraint with list partition 2533CREATE TABLE t1( 2534id INT NOT NULL CHECK(id BETWEEN 10 AND 50), 2535name VARCHAR(10) 2536) 2537PARTITION BY LIST(id) ( 2538PARTITION p0 VALUES IN (10,19), 2539PARTITION p1 VALUES IN (20,29), 2540PARTITION p2 VALUES IN (30,39), 2541PARTITION p3 VALUES IN (40,49) 2542); 2543SHOW CREATE TABLE t1; 2544Table Create Table 2545t1 CREATE TABLE `t1` ( 2546 `id` int NOT NULL, 2547 `name` varchar(10) DEFAULT NULL, 2548 CONSTRAINT `t1_chk_1` CHECK ((`id` between 10 and 50)) 2549) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2550/*!50100 PARTITION BY LIST (`id`) 2551(PARTITION p0 VALUES IN (10,19) ENGINE = InnoDB, 2552 PARTITION p1 VALUES IN (20,29) ENGINE = InnoDB, 2553 PARTITION p2 VALUES IN (30,39) ENGINE = InnoDB, 2554 PARTITION p3 VALUES IN (40,49) ENGINE = InnoDB) */ 2555INSERT INTO t1 VALUES(60,'aaa'); 2556ERROR HY000: Check constraint 't1_chk_1' is violated. 2557INSERT INTO t1 VALUES(30,'aaa'); 2558SELECT * FROM t1; 2559id name 256030 aaa 2561DROP TABLE t1; 2562# check constraint with hash partition 2563CREATE TABLE t1(id INT NOT NULL CHECK(id > 10), 2564name VARCHAR(40) 2565) 2566PARTITION BY HASH(id) 2567PARTITIONS 4; 2568SHOW CREATE TABLE t1; 2569Table Create Table 2570t1 CREATE TABLE `t1` ( 2571 `id` int NOT NULL, 2572 `name` varchar(40) DEFAULT NULL, 2573 CONSTRAINT `t1_chk_1` CHECK ((`id` > 10)) 2574) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2575/*!50100 PARTITION BY HASH (`id`) 2576PARTITIONS 4 */ 2577INSERT INTO t1 VALUES(1,'aaa'); 2578ERROR HY000: Check constraint 't1_chk_1' is violated. 2579INSERT INTO t1 VALUES(60,'aaa'); 2580SELECT * FROM t1; 2581id name 258260 aaa 2583DROP TABLE t1; 2584# check constraint with key partition 2585CREATE TABLE t1(id INT PRIMARY KEY NOT NULL CHECK(id > 10), 2586name VARCHAR(40) 2587) 2588PARTITION BY KEY() 2589PARTITIONS 4; 2590SHOW CREATE TABLE t1; 2591Table Create Table 2592t1 CREATE TABLE `t1` ( 2593 `id` int NOT NULL, 2594 `name` varchar(40) DEFAULT NULL, 2595 PRIMARY KEY (`id`), 2596 CONSTRAINT `t1_chk_1` CHECK ((`id` > 10)) 2597) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2598/*!50100 PARTITION BY KEY () 2599PARTITIONS 4 */ 2600INSERT INTO t1 VALUES(1,'aaa'); 2601ERROR HY000: Check constraint 't1_chk_1' is violated. 2602INSERT INTO t1 VALUES(60,'aaa'); 2603SELECT * FROM t1; 2604id name 260560 aaa 2606DROP TABLE t1; 2607#----------------------------------------------------------------------- 2608# Test case to verify check constraint with Updatable view 2609#----------------------------------------------------------------------- 2610CREATE TABLE t1(c1 INT, c2 INT CHECK (c2 < 10)); 2611CREATE VIEW v1 AS SELECT * FROM t1; 2612INSERT INTO v1 VALUES(1,20); 2613ERROR HY000: Check constraint 't1_chk_1' is violated. 2614INSERT INTO v1 VALUES(1,5); 2615SELECT * FROM t1; 2616c1 c2 26171 5 2618SELECT * FROM v1; 2619c1 c2 26201 5 2621DROP VIEW v1; 2622DROP TABLE t1; 2623#----------------------------------------------------------------------- 2624# Test case to verify error reporting when check constraint evaluation 2625# fails due to type conversion issue. 2626#----------------------------------------------------------------------- 2627CREATE TABLE t1 (f1 CHAR(10) CHECK (f1 < 10)); 2628INSERT INTO t1 VALUES ("xy"); 2629ERROR HY000: Check constraint 't1_chk_1' is violated. 2630# Show warnings lists error reported for type conversion issue too. 2631SHOW WARNINGS; 2632Level Code Message 2633Error 1292 Truncated incorrect DOUBLE value: 'xy' 2634Error 3819 Check constraint 't1_chk_1' is violated. 2635DROP TABLE t1; 2636#----------------------------------------------------------------------- 2637# Bug#29191994 - MULTIPLE CONSTRAINTS ARE NOT ACCEPTED WHEN FIRST IS 2638# CHECK CONSTRAINT IN COLUMN. 2639#----------------------------------------------------------------------- 2640CREATE TABLE t1(a INTEGER CHECK (a > 0) NOT NULL); 2641SHOW CREATE TABLE t1; 2642Table Create Table 2643t1 CREATE TABLE `t1` ( 2644 `a` int NOT NULL, 2645 CONSTRAINT `t1_chk_1` CHECK ((`a` > 0)) 2646) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2647CREATE TABLE t2(a INTEGER CHECK (a > 0) UNIQUE); 2648SHOW CREATE TABLE t2; 2649Table Create Table 2650t2 CREATE TABLE `t2` ( 2651 `a` int DEFAULT NULL, 2652 UNIQUE KEY `a` (`a`), 2653 CONSTRAINT `t2_chk_1` CHECK ((`a` > 0)) 2654) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2655CREATE TABLE t3(a INTEGER CHECK (a > 0) PRIMARY KEY); 2656SHOW CREATE TABLE t3; 2657Table Create Table 2658t3 CREATE TABLE `t3` ( 2659 `a` int NOT NULL, 2660 PRIMARY KEY (`a`), 2661 CONSTRAINT `t3_chk_1` CHECK ((`a` > 0)) 2662) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2663CREATE TABLE t4(a INTEGER CHECK (a > 0) ENFORCED NOT NULL); 2664SHOW CREATE TABLE t4; 2665Table Create Table 2666t4 CREATE TABLE `t4` ( 2667 `a` int NOT NULL, 2668 CONSTRAINT `t4_chk_1` CHECK ((`a` > 0)) 2669) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2670CREATE TABLE t5(a INTEGER CHECK (a > 0) NOT ENFORCED NOT NULL); 2671SHOW CREATE TABLE t5; 2672Table Create Table 2673t5 CREATE TABLE `t5` ( 2674 `a` int NOT NULL, 2675 CONSTRAINT `t5_chk_1` CHECK ((`a` > 0)) /*!80016 NOT ENFORCED */ 2676) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2677CREATE TABLE t6(a INTEGER CHECK (a > 0) UNIQUE CHECK (a IS NOT NULL) NULL CHECK (a < 100)); 2678SHOW CREATE TABLE t6; 2679Table Create Table 2680t6 CREATE TABLE `t6` ( 2681 `a` int DEFAULT NULL, 2682 UNIQUE KEY `a` (`a`), 2683 CONSTRAINT `t6_chk_1` CHECK ((`a` > 0)), 2684 CONSTRAINT `t6_chk_2` CHECK ((`a` is not null)), 2685 CONSTRAINT `t6_chk_3` CHECK ((`a` < 100)) 2686) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2687CREATE TABLE t7(a INTEGER CHECK (a > 0) ENFORCED NOT NULL); 2688# [NOT] ENFORCED must follow check constraint clause. Error is reported otherwise. 2689CREATE TABLE t8(a INTEGER ENFORCED); 2690ERROR 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 ' ENFORCED)' at line 1 2691CREATE TABLE t8(a INTEGER NOT ENFORCED); 2692ERROR 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 'NOT ENFORCED)' at line 1 2693CREATE TABLE t8(a INTEGER AUTO_INCREMENT NOT ENFORCED); 2694ERROR 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 'NOT ENFORCED)' at line 1 2695# Error is reported if column check constraints reference other columns of the 2696# table. Following cases verify the same when multiple check constraints are 2697# defined for a column. 2698CREATE TABLE t8(a INTEGER, b INTEGER CHECK (a + b > 0) UNIQUE CHECK ( a - b < 1000)); 2699ERROR HY000: Column check constraint 't8_chk_1' references other column. 2700CREATE TABLE t7(a INTEGER, b INTEGER CHECK (b > 0) UNIQUE CHECK ( a - b < 1000)); 2701ERROR HY000: Column check constraint 't7_chk_2' references other column. 2702DROP TABLE t1,t2,t3,t4,t5,t6,t7; 2703#----------------------------------------------------------------------- 2704# Bug#29706621 - CHECK CONSTRAINT COMPARING COLUMNS IS NOT ALWAYS 2705# ENFORCED WITH UPDATE QUERIES. 2706#----------------------------------------------------------------------- 2707SET @binlog_format_saved = @@binlog_format; 2708# In default case, when ROW format and full row image are used the bug 2709# is hidden, as all columns are marked as modified by UPDATE. 2710SET binlog_format = 'STATEMENT'; 2711CREATE TABLE tst ( 2712id INT, 2713start_date DATE, 2714end_date DATE, 2715PRIMARY KEY (id), 2716CONSTRAINT chk_dat CHECK (end_date > start_date) 2717); 2718INSERT INTO tst (id, start_date, end_date) VALUES (1, '2019-04-25', '2019-04-30'); 2719# Without fix, check constraint is not evaluated and following statement succeeds. 2720# With fix, error is reported. 2721UPDATE tst SET end_date = '2019-04-20' WHERE id = 1; 2722ERROR HY000: Check constraint 'chk_dat' is violated. 2723UPDATE tst SET start_date = '2019-05-01' WHERE id = 1; 2724ERROR HY000: Check constraint 'chk_dat' is violated. 2725UPDATE tst SET id = 5 WHERE start_date = '2019-04-25'; 2726UPDATE tst SET id = 6, start_date = '2019-05-02', end_date = '2019-04-23' WHERE id = 5; 2727ERROR HY000: Check constraint 'chk_dat' is violated. 2728UPDATE tst SET id = 6, start_date = '2019-05-02', end_date = '2049-04-23' WHERE id = 5; 2729DROP TABLE tst; 2730SET binlog_format=@binlog_format_saved; 2731#----------------------------------------------------------------------- 2732# Bug#29652464 - ORDER OF ADD AND DROP CHECK CONSTRAINT IN TABLE ALTER 2733# STATEMENT IS INCORRECT. 2734#----------------------------------------------------------------------- 2735CREATE TABLE t1 (f1 INT CONSTRAINT ck1 CHECK (f1 > 0), f2 INT); 2736SHOW CREATE TABLE t1; 2737Table Create Table 2738t1 CREATE TABLE `t1` ( 2739 `f1` int DEFAULT NULL, 2740 `f2` int DEFAULT NULL, 2741 CONSTRAINT `ck1` CHECK ((`f1` > 0)) 2742) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2743# Without fix, following statement succeeds. With fix, error is 2744# reported(as expected) for drop operation on non-existing check 2745# constraint ck2. 2746ALTER TABLE t1 ADD CONSTRAINT ck2 CHECK (f2 > 0), DROP CHECK ck2; 2747ERROR HY000: Check constraint 'ck2' is not found in the table. 2748# Existing check constraint ck1 is dropped and new constraint is 2749# created with the same name. 2750ALTER TABLE t1 ADD CONSTRAINT ck1 CHECK (f2 > 0), DROP CHECK ck1; 2751SHOW CREATE TABLE t1; 2752Table Create Table 2753t1 CREATE TABLE `t1` ( 2754 `f1` int DEFAULT NULL, 2755 `f2` int DEFAULT NULL, 2756 CONSTRAINT `ck1` CHECK ((`f2` > 0)) 2757) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2758# ck1 is auto-dropped on dropping column f2. New constraint with 2759# same name ck1 is added. 2760ALTER TABLE t1 DROP COLUMN f2, ADD CONSTRAINT ck1 CHECK (f1 > 0); 2761SHOW CREATE TABLE t1; 2762Table Create Table 2763t1 CREATE TABLE `t1` ( 2764 `f1` int DEFAULT NULL, 2765 CONSTRAINT `ck1` CHECK ((`f1` > 0)) 2766) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2767# ck1 is auto-dropped on dropping column f1. New column f1 and check 2768# constraint on f1 are added. 2769ALTER TABLE t1 DROP COLUMN f1, ADD COLUMN f1 BIGINT, ADD CONSTRAINT CHECK (f1!= 0); 2770SHOW CREATE TABLE t1; 2771Table Create Table 2772t1 CREATE TABLE `t1` ( 2773 `f1` bigint DEFAULT NULL, 2774 CONSTRAINT `t1_chk_1` CHECK ((`f1` <> 0)) 2775) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2776DROP TABLE t1; 2777######################################################################### 2778# Bug#29706689 - CHECK CONSTRAINT COMPARING COLUMN WITH DEFAULT VALUE IS 2779# NOT ENFORCED. 2780######################################################################### 2781#------------------------------------------------------------------------ 2782# Case 1: Simple test case to verify check constraint expression evaluation 2783# with the column using function CURDATE() as default. 2784#------------------------------------------------------------------------ 2785CREATE TABLE tst ( 2786id INT, 2787start_date DATE, 2788end_date DATE, 2789created DATE DEFAULT (CURDATE()), 2790PRIMARY KEY (id), 2791CONSTRAINT chk_dat CHECK (start_date >= created) 2792); 2793INSERT INTO tst (id, start_date) VALUES (1, CURDATE()); 2794# Without fix, check constraint chk_dat evaluation succeeds. With fix, 2795# check constraint evaluation fails and an error is reported. 2796INSERT INTO tst (id, start_date) VALUES (2, '2019-04-25'); 2797ERROR HY000: Check constraint 'chk_dat' is violated. 2798DROP TABLE tst; 2799#------------------------------------------------------------------------ 2800# Case 2: Test case to verify check constraint expression evaluation 2801# with the column using function CURDATE() as default. 2802# Test case verifies behavior with INSERT, REPLACE and LOAD 2803# operations. 2804#------------------------------------------------------------------------ 2805SET TIME_ZONE = "+00:00"; 2806#Time set to May 7, 2019 17:51:02 GMT 2807SET TIMESTAMP=1557251462; 2808CREATE TABLE tst (id INT PRIMARY KEY, 2809scol DATE DEFAULT(CURDATE()), 2810col DATE, 2811CHECK ( scol < col)); 2812SHOW CREATE TABLE tst; 2813Table Create Table 2814tst CREATE TABLE `tst` ( 2815 `id` int NOT NULL, 2816 `scol` date DEFAULT (curdate()), 2817 `col` date DEFAULT NULL, 2818 PRIMARY KEY (`id`), 2819 CONSTRAINT `tst_chk_1` CHECK ((`scol` < `col`)) 2820) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2821# INSERT with valid values. 2822INSERT INTO tst(id, col) VALUES (1, '2019-05-20'); 2823# Check constraint evaluation (after setting default value) fails during 2824# INSERT operation. 2825INSERT INTO tst(id, col) VALUES (1, '2019-05-06'); 2826ERROR HY000: Check constraint 'tst_chk_1' is violated. 2827# Check constraint evaluation (after setting default value) fails during 2828# REPLACE operation. 2829REPLACE INTO tst(id, col) VALUES (2, '2019-05-06'); 2830ERROR HY000: Check constraint 'tst_chk_1' is violated. 2831# REPLACE with valid values. 2832REPLACE INTO tst(id, col) VALUES (2, '2019-05-20'); 2833# Check constraint evaluation (after setting default value) fails during 2834# LOAD operation. 2835CREATE TABLE tmp(id INT, col DATE); 2836INSERT INTO tmp VALUES(3, '2019-05-06'); 2837SELECT * FROM tmp INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 2838# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 2839# the table "tmp". Check constraint evaluation fails during LOAD operation 2840# with the value from tmp1.txt. 2841LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE tst(id, col);; 2842ERROR HY000: Check constraint 'tst_chk_1' is violated. 2843# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 2844# from the table "tmp". Check constraint evaluation fails during LOAD 2845# operation with the value from tmp1.xml. 2846LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE tst(id, col);; 2847ERROR HY000: Check constraint 'tst_chk_1' is violated. 2848DROP TABLE tmp; 2849DELETE FROM tst; 2850#------------------------------------------------------------------------ 2851# Case 3: Test case to verify check constraint expression evaluation 2852# with the column using function CURTIME() as default. 2853# Test case verifies behavior with INSERT, REPLACE and LOAD 2854# operations. 2855#------------------------------------------------------------------------ 2856ALTER TABLE tst MODIFY COLUMN scol TIME DEFAULT(CURTIME()), MODIFY COLUMN col TIME; 2857SHOW CREATE TABLE tst; 2858Table Create Table 2859tst CREATE TABLE `tst` ( 2860 `id` int NOT NULL, 2861 `scol` time DEFAULT (curtime()), 2862 `col` time DEFAULT NULL, 2863 PRIMARY KEY (`id`), 2864 CONSTRAINT `tst_chk_1` CHECK ((`scol` < `col`)) 2865) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2866# INSERT with valid values. 2867INSERT INTO tst(id, col) VALUES (1, '20:20:20'); 2868# Check constraint evaluation (after setting default value) fails during 2869# INSERT operation. 2870INSERT INTO tst(id, col) VALUES (1, '15:15:15'); 2871ERROR HY000: Check constraint 'tst_chk_1' is violated. 2872# Check constraint evaluation (after setting default value) fails during 2873# REPLACE operation. 2874REPLACE INTO tst(id, col) VALUES (2, '15:15:15'); 2875ERROR HY000: Check constraint 'tst_chk_1' is violated. 2876# REPLACE with valid values. 2877REPLACE INTO tst(id, col) VALUES (2, '20:20:20'); 2878# Check constraint evaluation (after setting default value) fails during 2879# LOAD operation. 2880CREATE TABLE tmp(id INT, col TIME); 2881INSERT INTO tmp VALUES(3, '15:15:15'); 2882SELECT * FROM tmp INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 2883# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 2884# the table "tmp". Check constraint evaluation fails during LOAD operation 2885# with the value from tmp1.txt. 2886LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE tst(id, col);; 2887ERROR HY000: Check constraint 'tst_chk_1' is violated. 2888# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 2889# from the table "tmp". Check constraint evaluation fails during LOAD 2890# operation with the value from tmp1.xml. 2891# LOAD XML with invalid values. 2892LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE tst(id, col);; 2893ERROR HY000: Check constraint 'tst_chk_1' is violated. 2894DROP TABLE tmp; 2895DELETE FROM tst; 2896#------------------------------------------------------------------------ 2897# Case 4: Test case to verify check constraint expression evaluation 2898# with the column of type "timestamp" using function 2899# CURRENT_TIMESTAMP() as default. 2900# Test case verifies behavior with INSERT, REPLACE, UPDATE, 2901# INSERT ON DUPLICATE KEY UPDATE and LOAD operations. 2902#------------------------------------------------------------------------ 2903ALTER TABLE tst MODIFY COLUMN scol timestamp DEFAULT(CURRENT_TIMESTAMP()), 2904MODIFY COLUMN col timestamp; 2905SHOW CREATE TABLE tst; 2906Table Create Table 2907tst CREATE TABLE `tst` ( 2908 `id` int NOT NULL, 2909 `scol` timestamp NULL DEFAULT (now()), 2910 `col` timestamp NULL DEFAULT NULL, 2911 PRIMARY KEY (`id`), 2912 CONSTRAINT `tst_chk_1` CHECK ((`scol` < `col`)) 2913) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2914# INSERT with valid values. 2915INSERT INTO tst(id, col) VALUES (1, '2019-05-20 12:12:12'); 2916# Check constraint evaluation (after setting default value) fails during 2917# INSERT operation. 2918INSERT INTO tst(id, col) VALUES (1, '2019-05-06 12:12:12'); 2919ERROR HY000: Check constraint 'tst_chk_1' is violated. 2920# Check constraint evaluation (after setting default value) fails during 2921# REPLACE operation. 2922REPLACE INTO tst(id, col) VALUES (2, '2019-05-06 12:12:12'); 2923ERROR HY000: Check constraint 'tst_chk_1' is violated. 2924# REPLACE with valid values. 2925REPLACE INTO tst(id, col) VALUES (2, '2019-05-20 12:12:12'); 2926# Check constraint evaluation (after setting default value) fails during 2927# LOAD operation. 2928CREATE TABLE tmp(id INT, col TIMESTAMP); 2929INSERT INTO tmp VALUES(3, '2019-05-06 12:12:12'); 2930SELECT * FROM tmp INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 2931# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 2932# the table "tmp". Check constraint evaluation fails during LOAD operation 2933# with the value from tmp1.txt. 2934LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE tst(id, col);; 2935ERROR HY000: Check constraint 'tst_chk_1' is violated. 2936# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 2937# from the table "tmp". Check constraint evaluation fails during LOAD 2938# operation with the value from tmp1.xml. 2939LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE tst(id, col);; 2940ERROR HY000: Check constraint 'tst_chk_1' is violated. 2941# Cases to verify behavior with ON UPDATE CURRENT_TIMESTAMP 2942ALTER TABLE tst MODIFY COLUMN scol timestamp ON UPDATE CURRENT_TIMESTAMP; 2943SHOW CREATE TABLE tst; 2944Table Create Table 2945tst CREATE TABLE `tst` ( 2946 `id` int NOT NULL, 2947 `scol` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, 2948 `col` timestamp NULL DEFAULT NULL, 2949 PRIMARY KEY (`id`), 2950 CONSTRAINT `tst_chk_1` CHECK ((`scol` < `col`)) 2951) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2952#Time set to May 25, 2019 21:09:09 GMT 2953SET TIMESTAMP=1558818549; 2954# Check constraint evaluation (after setting on update value) fails during 2955# UPDATE ON DUPLICATE KEY. 2956INSERT INTO tst(id, col) VALUES (1, '2019-05-20 12:12:12') ON DUPLICATE KEY UPDATE id=3; 2957ERROR HY000: Check constraint 'tst_chk_1' is violated. 2958# Check constraint evaluation (after setting on update value) fails during 2959# UPDATE operation. 2960UPDATE tst SET col='2019-05-21 12:12:12' WHERE id = 1; 2961ERROR HY000: Check constraint 'tst_chk_1' is violated. 2962# Check constraint evaluation (after setting on update value) fails during 2963# multi-table UPDATE operation. 2964CREATE TABLE tst1 (id INT, col timestamp DEFAULT('2019-05-21 12:12:12')); 2965SHOW CREATE TABLE tst1; 2966Table Create Table 2967tst1 CREATE TABLE `tst1` ( 2968 `id` int DEFAULT NULL, 2969 `col` timestamp NULL DEFAULT (_utf8mb4'2019-05-21 12:12:12') 2970) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2971INSERT INTO tst1(id) VALUES(1); 2972UPDATE tst,tst1 SET tst.col = tst1.col WHERE tst.id = tst1.id; 2973ERROR HY000: Check constraint 'tst_chk_1' is violated. 2974DROP TABLE tmp; 2975DELETE FROM tst; 2976DELETE FROM tst1; 2977#------------------------------------------------------------------------ 2978# Case 5: Test cases to verify check constraint expression evaluation 2979# with the column of type "datetime" using function 2980# CURRENT_TIMESTAMP() as default. 2981# Test case verifies behavior with INSERT, REPLACE, UPDATE, 2982# INSERT ON DUPLICATE KEY UPDATE and LOAD operations. 2983#------------------------------------------------------------------------ 2984#Time set to May 7, 2019 17:51:02 GMT 2985SET TIMESTAMP=1557251462; 2986ALTER TABLE tst MODIFY COLUMN scol datetime DEFAULT(CURRENT_TIMESTAMP()), 2987MODIFY COLUMN col datetime; 2988SHOW CREATE TABLE tst; 2989Table Create Table 2990tst CREATE TABLE `tst` ( 2991 `id` int NOT NULL, 2992 `scol` datetime DEFAULT (now()), 2993 `col` datetime DEFAULT NULL, 2994 PRIMARY KEY (`id`), 2995 CONSTRAINT `tst_chk_1` CHECK ((`scol` < `col`)) 2996) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 2997# INSERT with valid values. 2998INSERT INTO tst(id, col) VALUES (1, '2019-05-20 12:12:12'); 2999# Check constraint evaluation (after setting default value) fails during 3000# INSERT operation. 3001INSERT INTO tst(id, col) VALUES (1, '2019-05-06 12:12:12'); 3002ERROR HY000: Check constraint 'tst_chk_1' is violated. 3003# Check constraint evaluation (after setting default value) fails during 3004# REPLACE operation. 3005REPLACE INTO tst(id, col) VALUES (2, '2019-05-06 12:12:12'); 3006ERROR HY000: Check constraint 'tst_chk_1' is violated. 3007# REPLACE with valid values. 3008REPLACE INTO tst(id, col) VALUES (2, '2019-05-20 12:12:12'); 3009# Check constraint evaluation (after setting default value) fails during 3010# REPLACE operation. 3011REPLACE INTO tst(id, col) VALUES (2, '2019-05-06 12:12:12'); 3012ERROR HY000: Check constraint 'tst_chk_1' is violated. 3013# Check constraint evaluation (after setting default value) fails during 3014# LOAD operation. 3015CREATE TABLE tmp(id INT, col TIMESTAMP); 3016INSERT INTO tmp VALUES(3, '2019-05-06 12:12:12'); 3017SELECT * FROM tmp INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 3018# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 3019# the table "tmp". Check constraint evaluation fails during LOAD operation 3020# with the value from tmp1.txt. 3021LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE tst(id, col);; 3022ERROR HY000: Check constraint 'tst_chk_1' is violated. 3023# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 3024# from the table "tmp". Check constraint evaluation fails during LOAD 3025# operation with the value from tmp1.xml. 3026LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE tst(id, col);; 3027ERROR HY000: Check constraint 'tst_chk_1' is violated. 3028# Cases to verify behavior with ON UPDATE CURRENT_TIMESTAMP 3029ALTER TABLE tst MODIFY COLUMN scol datetime ON UPDATE CURRENT_TIMESTAMP; 3030SHOW CREATE TABLE tst; 3031Table Create Table 3032tst CREATE TABLE `tst` ( 3033 `id` int NOT NULL, 3034 `scol` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, 3035 `col` datetime DEFAULT NULL, 3036 PRIMARY KEY (`id`), 3037 CONSTRAINT `tst_chk_1` CHECK ((`scol` < `col`)) 3038) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3039#Time set to May 25, 2019 21:09:09 GMT 3040SET TIMESTAMP=1558818549; 3041# Check constraint evaluation (after setting on update value) fails during 3042# UPDATE ON DUPLICATE KEY. 3043INSERT INTO tst(id, col) VALUES (1, '2019-05-20 12:12:12') ON DUPLICATE KEY UPDATE id=3; 3044ERROR HY000: Check constraint 'tst_chk_1' is violated. 3045# Check constraint evaluation (after setting on update value) fails during 3046# UPDATE operation. 3047UPDATE tst SET col='2019-05-21 12:12:12' WHERE id = 1; 3048ERROR HY000: Check constraint 'tst_chk_1' is violated. 3049# Check constraint evaluation (after setting on update value) fails during 3050# multi-table UPDATE operation. 3051ALTER TABLE tst1 MODIFY COLUMN col datetime DEFAULT('2019-05-21 12:12:12'); 3052SHOW CREATE TABLE tst1; 3053Table Create Table 3054tst1 CREATE TABLE `tst1` ( 3055 `id` int DEFAULT NULL, 3056 `col` datetime DEFAULT (_utf8mb4'2019-05-21 12:12:12') 3057) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3058INSERT INTO tst1(id) VALUES(1); 3059UPDATE tst,tst1 SET tst.col = tst1.col WHERE tst.id = tst1.id; 3060ERROR HY000: Check constraint 'tst_chk_1' is violated. 3061DROP TABLE tmp, tst, tst1; 3062SET TIMESTAMP=DEFAULT; 3063SET TIME_ZONE=DEFAULT; 3064#------------------------------------------------------------------------ 3065# Case 6: Test case to verify check constraint expression evaluation 3066# with the column using default expression. 3067# Test case verifies behavior with INSERT, REPLACE, UPDATE, 3068# and LOAD operations. 3069#------------------------------------------------------------------------ 3070CREATE TABLE tst (id INT PRIMARY KEY, 3071scol INT DEFAULT(col * col), 3072col INT, 3073CHECK ( scol < col)); 3074SHOW CREATE TABLE tst; 3075Table Create Table 3076tst CREATE TABLE `tst` ( 3077 `id` int NOT NULL, 3078 `scol` int DEFAULT ((`col` * `col`)), 3079 `col` int DEFAULT NULL, 3080 PRIMARY KEY (`id`), 3081 CONSTRAINT `tst_chk_1` CHECK ((`scol` < `col`)) 3082) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3083# INSERT with valid values. 3084INSERT INTO tst VALUES (1, 10, 20); 3085SELECT * FROM tst; 3086id scol col 30871 10 20 3088# Check constraint evaluation (after setting default value) fails during 3089# INSERT operation. 3090INSERT INTO tst(id, col) VALUES (2, 10); 3091ERROR HY000: Check constraint 'tst_chk_1' is violated. 3092# Check constraint evaluation (after setting default value) fails during 3093# REPLACE operation. 3094REPLACE INTO tst(id, col) VALUES (2, 10); 3095ERROR HY000: Check constraint 'tst_chk_1' is violated. 3096# REPLACE with valid values. 3097REPLACE INTO tst VALUES (2, 10, 20); 3098# Check constraint evaluation (after setting default value) fails during 3099# LOAD operation. 3100CREATE TABLE tmp(id INT, col INT); 3101INSERT INTO tmp VALUES(3, 10); 3102SELECT * FROM tmp INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 3103# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 3104# the table "tmp". Check constraint evaluation fails during LOAD operation 3105# with the value from tmp1.txt. 3106LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE tst(id, col);; 3107ERROR HY000: Check constraint 'tst_chk_1' is violated. 3108# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 3109# from the table "tmp". Check constraint evaluation fails during LOAD 3110# operation with the value from tmp1.xml. 3111LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE tst(id, col);; 3112ERROR HY000: Check constraint 'tst_chk_1' is violated. 3113DROP TABLE tst, tmp; 3114#------------------------------------------------------------------------ 3115# Case 7: Test case to verify set function defaults, before trigger, 3116# CHECK OPTION and Check constraint execution order with the 3117# INSERT, REPLACE, UPDATE, INSERT ON DUPLICATE KEY UPDATE and 3118# LOAD operations. 3119# The execution order should be, 3120# 1. Set function defaults (using CURRENT_TIMESTAMP here) 3121# 2. Before triggers 3122# 3. View CHECK OPTION 3123# 4. CHECK CONSTRAINT 3124#------------------------------------------------------------------------ 3125CREATE TABLE t1 (f1 INT PRIMARY KEY, 3126f2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 3127CHECK (f2 < '2018-01-01 00:00:00')); 3128INSERT INTO t1 VALUES (4, '2017-06-06 00:00:00'), 3129(5, '2017-06-06 00:00:00'), 3130(6, '2017-06-06 00:00:00'); 3131CREATE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f2 < '2019-01-01 00:00:00' WITH CHECK OPTION; 3132CREATE TRIGGER t1_before_insert_trg BEFORE INSERT ON t1 FOR EACH ROW 3133BEGIN 3134IF NEW.f1 = 1 THEN 3135-- Valid value case. 3136SET NEW.f2 = '2017-06-06 00:00:00'; 3137ELSEIF NEW.f1 = 2 THEN 3138-- Check option failure case. 3139SET NEW.f2 = '2019-06-06 00:00:00'; 3140ELSEIF NEW.f1 = 3 THEN 3141-- Check constraint failure case. 3142SET NEW.f2 = '2018-06-06 00:00:00'; 3143END IF; 3144END;$ 3145CREATE TRIGGER t1_before_update_trg BEFORE UPDATE ON t1 FOR EACH ROW 3146BEGIN 3147IF OLD.f1 = 4 THEN 3148-- Valid value case. 3149SET NEW.f2 = '2017-06-06 00:00:00'; 3150ELSEIF OLD.f1 = 5 THEN 3151-- Check option failure case. 3152SET NEW.f2 = '2019-06-06 00:00:00'; 3153ELSEIF OLD.f1 = 6 THEN 3154-- Check constraint failure case. 3155SET NEW.f2 = '2018-06-06 00:00:00'; 3156END IF; 3157END;$ 3158#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3159# INSERT operations. 3160#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3161# INSERT with valid values. 3162# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE INSERT 3163# trigger with valid values. CHECK OPTION and CHECK CONSTRAINT passes 3164# with the adjusted value (fails without adjustment). 3165# Which means BEFORE trigger is executed after setting function defaults 3166# and before executing CHECK OPTION and CHECK CONSTRAINTS. 3167INSERT INTO v1(f1) VALUES(1); 3168# INSERT with invalid value. View CHECK OPTION fails. 3169# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE INSERT 3170# trigger with the invalid value (CHECK OPTION and CHECK CONSTRAINT fails 3171# with this value). Error from CHECK OPTION is reported in this case. 3172# Which means CHECK OPTION is executed after BEFORE trigger and before 3173# CHECK CONSTRAINTS. 3174INSERT INTO v1(f1) VALUES(2); 3175ERROR HY000: CHECK OPTION failed 'test.v1' 3176# INSERT with invalid value. CHECK CONSTRAINT evaluation fails. 3177# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE INSERT 3178# trigger invalid value (Only CHECK CONSTRAINT evaluation fails with this 3179# value. CHECK OPTION passes). Error from CHECK CONSTRAINT evaluation is 3180# reported in this case. CHECK CONSTRAINT is evaluated after CHECK OPTION. 3181INSERT INTO v1(f1) VALUES(3); 3182ERROR HY000: Check constraint 't1_chk_1' is violated. 3183#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3184# INSERT ... ON DUPLICATE UPDATE operations. 3185#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3186# INSERT with valid values. 3187# When update part of statement is processed f2 gets CURRENT_TIMESTAMP as 3188# its value as result of ON UPDATE clause, which is adjusted by the BEFORE 3189# UPDATE trigger. CHECK OPTION and CHECK CONSTRAINT evaluation passes with 3190# adjusted value (fails without adjustment). 3191# Which means BEFORE trigger is executed after setting function defaults 3192# and before executing CHECK OPTION and CHECK CONSTRAINTS. 3193INSERT INTO v1 VALUES (4, '2017-01-01 00:00:00') ON DUPLICATE KEY UPDATE f1 = 7; 3194# INSERT with invalid value. View CHECK OPTION fails. 3195# When update part of statement is processed f2 gets CURRENT_TIMESTAMP as 3196# its value as result of ON UPDATE clause, which is adjusted by the BEFORE 3197# UPDATE trigger(CHECK OPTION and check constraint evaluation fails with 3198# this value). Error from CHECK OPTION is reported in this case. 3199# Which means CHECK OPTION is executed after BEFORE trigger and before 3200# CHECK CONSTRAINTS. 3201INSERT INTO v1 VALUES (5, '2017-01-01 00:00:00') ON DUPLICATE KEY UPDATE f1 = 7; 3202ERROR HY000: CHECK OPTION failed 'test.v1' 3203# INSERT with invalid value. CHECK CONSTRAINT evaluation fails. 3204# When update part of statement is processed f2 gets CURRENT_TIMESTAMP as 3205# its value as result of ON UPDATE clause, which is adjusted by the BEFORE 3206# UPDATE trigger(only check constraint evaluation fails with this value. 3207# CHECK OPTION passes). Error from CHECK CONSTRAINT evaluation is 3208# reported in this case. CHECK CONSTRAINT is evaluated after CHECK OPTION. 3209INSERT INTO v1 VALUES (6, '2017-01-01 00:00:00') ON DUPLICATE KEY UPDATE f1 = 7; 3210ERROR HY000: Check constraint 't1_chk_1' is violated. 3211#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3212# REPLACE operations. 3213#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3214# REPLACE with valid values. 3215# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE INSERT 3216# trigger with the valid values. CHECK OPTION and CHECK CONSTRAINT passes 3217# with adjusted value (fails without adjustment). 3218# Which means BEFORE trigger is executed after setting function defaults 3219# and before executing CHECK OPTION and CHECK CONSTRAINTS. 3220DELETE FROM v1 WHERE f1 = 7; 3221REPLACE INTO v1 VALUES(4, '2017-06-06 00:00:00'); 3222# REPLACE with invalid value. View CHECK OPTION fails. 3223# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE INSERT 3224# trigger with the invalid value (CHECK OPTION and CHECK CONSTRAINT evaluation 3225# fails with this value). Error from CHECK OPTION is reported in this case. 3226# Which means CHECK OPTION is executed after BEFORE trigger and before 3227# CHECK CONSTRAINTS. 3228REPLACE INTO v1(f1) VALUES(2); 3229ERROR HY000: CHECK OPTION failed 'test.v1' 3230# REPLACE with invalid value. CHECK CONSTRAINT evaluation fails. 3231# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE TRIGGER 3232# with the invalid value (Only CHECK CONSTRAINT evaluation fails with 3233# this value. CHECK OPTION passes). Error from CHECK CONSTRAINT evaluation is 3234# reported in this case. CHECK CONSTRAINT is evaluated after CHECK OPTION. 3235REPLACE INTO v1(f1) VALUES(3); 3236ERROR HY000: Check constraint 't1_chk_1' is violated. 3237#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3238# UPDATE operations. 3239#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3240# UPDATE with valid values. 3241# When update is processed f2 gets CURRENT_TIMESTAMP as its value as result 3242# of ON UPDATE clause, which is adjusted by the BEFORE UPDATE trigger. 3243# CHECK OPTION and CHECK CONSTRAINT evaluation passes with adjusted 3244# value (fails without adjustment). 3245# Which means BEFORE trigger is executed after setting function defaults 3246# and before executing CHECK OPTION and CHECK CONSTRAINTS. 3247UPDATE v1 SET f1 = 7 WHERE f1 = 4; 3248# When update is processed f2 gets CURRENT_TIMESTAMP as its value as result 3249# of ON UPDATE clause, which is adjusted by the BEFORE UPDATE trigger(CHECK 3250# OPTION and check constraint evaluation fails with this value). Error from 3251# CHECK OPTION is reported in this case. 3252# Which means CHECK OPTION is executed after BEFORE trigger and before 3253# CHECK CONSTRAINTS. 3254UPDATE v1 SET f1 = 8 WHERE f1 = 5; 3255ERROR HY000: CHECK OPTION failed 'test.v1' 3256# UPDATE with invalid value. CHECK CONSTRAINT evaluation fails. 3257# When update is processed f2 gets CURRENT_TIMESTAMP as its value as result 3258# of ON UPDATE clause, which is adjusted by the BEFORE UPDATE trigger 3259# (Only CHECK CONSTRAINT evaluation fails with this value. CHECK OPTION passes). 3260# Error from CHECK CONSTRAINT evaluation is reported in this case. CHECK 3261# CONSTRAINT is evaluated after CHECK OPTION. 3262UPDATE v1 SET f1 = 8 WHERE f1 = 6; 3263ERROR HY000: Check constraint 't1_chk_1' is violated. 3264UPDATE v1 SET f1 = 4, f2 = '2017-06-06 00:00:00' WHERE f1 = 7; 3265#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3266# LOAD operations. 3267#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3268CREATE TABLE t2 (f1 INT); 3269INSERT INTO t2 VALUES (1); 3270SELECT * FROM t2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 3271# LOAD with valid values. 3272# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE INSERT 3273# trigger with the valid values. CHECK OPTION and CHECK CONSTRAINT passes 3274# with adjusted value (fails without adjustment). 3275# Which means BEFORE trigger is executed after setting function defaults 3276# and before executing CHECK OPTION and CHECK CONSTRAINTS. 3277DELETE FROM t1 WHERE f1 = 1; 3278# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 3279# the table "t2". Check constraint evaluation succeeds with the value 3280# from tmp1.txt. 3281LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE v1(f1);; 3282DELETE FROM t1 WHERE f1 = 1; 3283# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 3284# from the table "t2". Check constraint evaluation succeeds with the 3285# value from tmp1.xml. 3286LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE v1(f1);; 3287# LOAD with invalid value. View CHECK OPTION fails. 3288# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE INSERT 3289# trigger with the invalid value (CHECK OPTION and CHECK CONSTRAINT evaluation 3290# fails with this value). Error from CHECK OPTION is reported in this case. 3291# Which means CHECK OPTION is executed after BEFORE trigger and before 3292# CHECK CONSTRAINTS. 3293DELETE FROM t2; 3294INSERT INTO t2 VALUES (2); 3295SELECT * FROM t2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 3296# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 3297# the table "t2". Check constraint evaluation fails during LOAD operation 3298# with the value from tmp1.txt. 3299LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE v1(f1);; 3300ERROR HY000: CHECK OPTION failed 'test.v1' 3301# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 3302# from the table "t2". Check constraint evaluation fails during LOAD 3303# operation with the value from tmp1.xml. 3304LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE v1(f1);; 3305ERROR HY000: CHECK OPTION failed 'test.v1' 3306# LOAD with invalid value. CHECK CONSTRAINT evaluation fails. 3307# Default CURRENT_TIMESTAMP value of f2 is adjusted by the BEFORE TRIGGER 3308# with the invalid value (Only CHECK CONSTRAINT evaluation fails with 3309# this value. CHECK OPTION passes). Error from CHECK CONSTRAINT evaluation is 3310# reported in this case. CHECK CONSTRAINT is evaluated after CHECK OPTION. 3311DELETE FROM t2; 3312INSERT INTO t2 VALUES (3); 3313SELECT * FROM t2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 3314# LOAD data in table from file tmp1.txt. tmp1.txt contains data from 3315# the table "t2". Check constraint evaluation fails during LOAD operation 3316# with the value from tmp1.txt. 3317LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' INTO TABLE v1(f1);; 3318ERROR HY000: Check constraint 't1_chk_1' is violated. 3319# LOAD data in table from file tmp1.xml. tmp1.xml contains data dumped 3320# from the table "t2". Check constraint evaluation fails during LOAD 3321# operation with the value from tmp1.xml. 3322LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" INTO TABLE v1(f1);; 3323ERROR HY000: Check constraint 't1_chk_1' is violated. 3324DROP TABLE t1, t2; 3325DROP VIEW v1; 3326######################################################################### 3327#----------------------------------------------------------------------- 3328# Bug#30084966 - LOAD DATA WITH IGNORE CLAUSE TERMINATES ON CHECK 3329# CONSTRAINT VIOLATION. 3330#----------------------------------------------------------------------- 3331CREATE TABLE t1 (f1 INT CHECK (f1 < 10), f2 CHAR(100)); 3332SHOW CREATE TABLE t1; 3333Table Create Table 3334t1 CREATE TABLE `t1` ( 3335 `f1` int DEFAULT NULL, 3336 `f2` char(100) DEFAULT NULL, 3337 CONSTRAINT `t1_chk_1` CHECK ((`f1` < 10)) 3338) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3339CREATE TABLE t2(f1 INT, f2 INT); 3340INSERT INTO t2 VALUES (1, 10), (20, 20), (3, 30), (4, 40); 3341SELECT * FROM t2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt';; 3342# Without fix, LOAD DATA terminates after check constraint violation with 3343# Row-2 (20, 20). Row-3 and Row-4 are not inserted to table t1. 3344# With fix, LOAD DATA continues to insert Row-3 and Row-4. 3345LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' IGNORE INTO TABLE t1;; 3346Warnings: 3347Warning 3819 Check constraint 't1_chk_1' is violated. 3348SHOW WARNINGS; 3349Level Code Message 3350Warning 3819 Check constraint 't1_chk_1' is violated. 3351SELECT * FROM t1; 3352f1 f2 33531 10 33543 30 33554 40 3356DELETE FROM t1; 3357# Test case to verify LOAD DATA with fixed-row format. 3358SELECT * FROM t2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/tmp1.txt' FIELDS TERMINATED BY '' ENCLOSED BY '';; 3359# Without fix, LOAD DATA terminates after check constraint violation with 3360# Row-2 (20, 20). Row-3 and Row-4 are not inserted to table t1. 3361# With fix, LOAD DATA continues to insert Row-3 and Row-4. 3362LOAD DATA INFILE "MYSQLTEST_VARDIR/tmp/tmp1.txt" IGNORE INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '';; 3363Warnings: 3364Warning 3819 Check constraint 't1_chk_1' is violated. 3365SHOW WARNINGS; 3366Level Code Message 3367Warning 3819 Check constraint 't1_chk_1' is violated. 3368SELECT * FROM t1; 3369f1 f2 33701 10 33713 30 33724 40 3373DELETE FROM t1; 3374# Test case added for coverage. LOAD XML works as expected. 3375LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" IGNORE INTO TABLE t1;; 3376Warnings: 3377Warning 3819 Check constraint 't1_chk_1' is violated. 3378SHOW WARNINGS; 3379Level Code Message 3380Warning 3819 Check constraint 't1_chk_1' is violated. 3381SELECT * FROM t1; 3382f1 f2 33831 10 33843 30 33854 40 3386DELETE FROM t1; 3387# Test case added for coverage. LOAD XML works as expected on CHECK 3388# OPTION violation. 3389# On CHECK OPTION violation for Row-2, LOAD DATA continues to insert 3390# Row-3 and Row-4 with IGNORE clause. 3391CREATE VIEW v1 AS SELECT f1, f2 FROM t1 WHERE f1 < 10 WITH CHECK OPTION; 3392LOAD XML INFILE "MYSQLTEST_VARDIR/tmp/tmp1.xml" IGNORE INTO TABLE v1;; 3393Warnings: 3394Warning 1369 CHECK OPTION failed 'test.v1' 3395SHOW WARNINGS; 3396Level Code Message 3397Warning 1369 CHECK OPTION failed 'test.v1' 3398SELECT * FROM v1; 3399f1 f2 34001 10 34013 30 34024 40 3403DROP VIEW v1; 3404DROP TABLE t1, t2; 3405#----------------------------------------------------------------------- 3406# Bug#29903865 - AUTO_INCREMENT_FIELD_NOT_NULL ASSERT FAIL AT 3407# TABLE::INIT WHILE SHOW CREATE TABLE. 3408#----------------------------------------------------------------------- 3409CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, b INT, CONSTRAINT c CHECK (b IS NULL)) IGNORE AS SELECT 1 AS id, 1 AS b; 3410Warnings: 3411Warning 3819 Check constraint 'c' is violated. 3412SHOW CREATE TABLE t1; 3413Table Create Table 3414t1 CREATE TABLE `t1` ( 3415 `id` int NOT NULL AUTO_INCREMENT, 3416 `b` int DEFAULT NULL, 3417 PRIMARY KEY (`id`), 3418 CONSTRAINT `c` CHECK ((`b` is null)) 3419) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3420DROP TABLE t1; 3421#----------------------------------------------------------------------- 3422# Bug#30239721 - ALTER TABLE RENAME RETURN IMPROPER ERROR MESSAGE FOR 3423# CHECK CONSTRAINT 3424#----------------------------------------------------------------------- 3425CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, f4 FLOAT AS (f3 * 0.01), f5 INT, 3426CHECK (f1 < f2)); 3427SHOW CREATE TABLE t1; 3428Table Create Table 3429t1 CREATE TABLE `t1` ( 3430 `f1` int DEFAULT NULL, 3431 `f2` int DEFAULT NULL, 3432 `f3` int DEFAULT NULL, 3433 `f4` float GENERATED ALWAYS AS ((`f3` * 0.01)) VIRTUAL, 3434 `f5` int DEFAULT NULL, 3435 CONSTRAINT `t1_chk_1` CHECK ((`f1` < `f2`)) 3436) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3437# Check constraint is dependent on column f1. Error is reported. 3438ALTER TABLE t1 DROP COLUMN f1; 3439ERROR HY000: Check constraint 't1_chk_1' uses column 'f1', hence column cannot be dropped or renamed. 3440# Check constraint is dependent on column f1. Error is reported. 3441ALTER TABLE t1 RENAME COLUMN f1 TO f6; 3442ERROR HY000: Check constraint 't1_chk_1' uses column 'f1', hence column cannot be dropped or renamed. 3443# Check constraint is dependent on column f1. Error is reported. 3444ALTER TABLE t1 CHANGE f1 f6 FLOAT; 3445ERROR HY000: Check constraint 't1_chk_1' uses column 'f1', hence column cannot be dropped or renamed. 3446# Renaming column to same name will not affect dependency. No error 3447# is reported in this case. 3448ALTER TABLE t1 RENAME COLUMN f1 TO f1; 3449ALTER TABLE t1 CHANGE f1 f1 FLOAT; 3450# Dropping a column used by a check constraint and creating a new column 3451# with the same name still reports an error. This behavior is similar to 3452# generated columns and default expressions. 3453ALTER TABLE t1 DROP COLUMN f1, ADD COLUMN f1 FLOAT; 3454ERROR HY000: Check constraint 't1_chk_1' uses column 'f1', hence column cannot be dropped or renamed. 3455ALTER TABLE t1 DROP COLUMN f3, ADD COLUMN f3 FLOAT; 3456ERROR HY000: Column 'f3' has a generated column dependency. 3457# Similar to drop column, even renaming column reports an error. 3458# This behavior is similar to generated columns and default expressions. 3459ALTER TABLE t1 RENAME COLUMN f1 TO f6, RENAME COLUMN f5 TO f1; 3460ERROR HY000: Check constraint 't1_chk_1' uses column 'f1', hence column cannot be dropped or renamed. 3461ALTER TABLE t1 RENAME COLUMN f3 TO f6, RENAME COLUMN f5 TO f3; 3462ERROR HY000: Column 'f3' has a generated column dependency. 3463# Adding a new check constraint with reference to column being dropped 3464# or renamed in the same ALTER statement reports unknown column error. 3465# This behavior is similar to generated columns and default expressions. 3466ALTER TABLE t1 DROP COLUMN f5, ADD CONSTRAINT CHECK (f5 < 10); 3467ERROR 42S22: Unknown column 'f5' in 'check constraint t1_chk_2 expression' 3468ALTER TABLE t1 DROP COLUMN f5, ADD COLUMN f7 FLOAT AS (f5 * 0.01); 3469ERROR 42S22: Unknown column 'f5' in 'generated column function' 3470ALTER TABLE t1 RENAME COLUMN f5 to f6, ADD CONSTRAINT CHECK (f5 < 10); 3471ERROR 42S22: Unknown column 'f5' in 'check constraint t1_chk_2 expression' 3472ALTER TABLE t1 RENAME COLUMN f5 to f6, ADD COLUMN f7 FLOAT AS (f5 * 0.01); 3473ERROR 42S22: Unknown column 'f5' in 'generated column function' 3474ALTER TABLE t1 CHANGE f5 f6 FLOAT, ADD CONSTRAINT CHECK (f5 < 10); 3475ERROR 42S22: Unknown column 'f5' in 'check constraint t1_chk_2 expression' 3476ALTER TABLE t1 CHANGE f5 f6 FLOAT, ADD COLUMN f7 FLOAT AS (f5 * 0.01); 3477ERROR 42S22: Unknown column 'f5' in 'generated column function' 3478# Adding a new check constraint with reference to column being renamed 3479# to same name. Table is altered in this case. 3480ALTER TABLE t1 RENAME COLUMN f2 TO f2, ADD CONSTRAINT CHECK(f2 < 1105); 3481ALTER TABLE t1 CHANGE f2 f2 FLOAT, ADD CONSTRAINT CHECK(f2 < 1105); 3482SHOW CREATE TABLE t1; 3483Table Create Table 3484t1 CREATE TABLE `t1` ( 3485 `f1` float DEFAULT NULL, 3486 `f2` float DEFAULT NULL, 3487 `f3` int DEFAULT NULL, 3488 `f4` float GENERATED ALWAYS AS ((`f3` * 0.01)) VIRTUAL, 3489 `f5` int DEFAULT NULL, 3490 CONSTRAINT `t1_chk_1` CHECK ((`f1` < `f2`)), 3491 CONSTRAINT `t1_chk_2` CHECK ((`f2` < 1105)), 3492 CONSTRAINT `t1_chk_3` CHECK ((`f2` < 1105)) 3493) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3494# Adding a new check constraint with reference to a new name of the 3495# column being renamed. Table is altered in this case. 3496ALTER TABLE t1 RENAME COLUMN f5 to f6, ADD CONSTRAINT CHECK (f6 < 10); 3497SHOW CREATE TABLE t1; 3498Table Create Table 3499t1 CREATE TABLE `t1` ( 3500 `f1` float DEFAULT NULL, 3501 `f2` float DEFAULT NULL, 3502 `f3` int DEFAULT NULL, 3503 `f4` float GENERATED ALWAYS AS ((`f3` * 0.01)) VIRTUAL, 3504 `f6` int DEFAULT NULL, 3505 CONSTRAINT `t1_chk_1` CHECK ((`f1` < `f2`)), 3506 CONSTRAINT `t1_chk_2` CHECK ((`f2` < 1105)), 3507 CONSTRAINT `t1_chk_3` CHECK ((`f2` < 1105)), 3508 CONSTRAINT `t1_chk_4` CHECK ((`f6` < 10)) 3509) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3510# Re-creating check constraint with reference to a new name of the 3511# column being renamed. Table is altered in this case. 3512ALTER TABLE t1 DROP CONSTRAINT t1_chk_1, RENAME COLUMN f1 to f11, 3513ADD CONSTRAINT t1_chk_1 CHECK (f11 < 10); 3514SHOW CREATE TABLE t1; 3515Table Create Table 3516t1 CREATE TABLE `t1` ( 3517 `f11` float DEFAULT NULL, 3518 `f2` float DEFAULT NULL, 3519 `f3` int DEFAULT NULL, 3520 `f4` float GENERATED ALWAYS AS ((`f3` * 0.01)) VIRTUAL, 3521 `f6` int DEFAULT NULL, 3522 CONSTRAINT `t1_chk_1` CHECK ((`f11` < 10)), 3523 CONSTRAINT `t1_chk_2` CHECK ((`f2` < 1105)), 3524 CONSTRAINT `t1_chk_3` CHECK ((`f2` < 1105)), 3525 CONSTRAINT `t1_chk_4` CHECK ((`f6` < 10)) 3526) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 3527DROP TABLE t1; 3528