1# 2# Check of check constraints 3 4set @save_check_constraint=@@check_constraint_checks; 5 6create table t1 (a int check(a>10), b int check (b > 20), constraint `min` check (a+b > 100), constraint `max` check (a+b <500)) engine=myisam; 7show create table t1; 8insert into t1 values (100,100); 9--error ER_CONSTRAINT_FAILED 10insert into t1 values (1,1); 11--error ER_CONSTRAINT_FAILED 12insert into t1 values (20,1); 13--error ER_CONSTRAINT_FAILED 14insert into t1 values (20,30); 15--error ER_CONSTRAINT_FAILED 16insert into t1 values (500,500); 17 18--error ER_CONSTRAINT_FAILED 19insert into t1 values (101,101),(102,102),(600,600),(103,103); 20select * from t1; 21truncate table t1; 22insert ignore into t1 values (101,101),(102,102),(600,600),(103,103); 23select * from t1; 24set check_constraint_checks=0; 25truncate table t1; 26insert into t1 values (101,101),(102,102),(600,600),(103,103); 27select * from t1; 28set check_constraint_checks=@save_check_constraint; 29 30--error ER_CONSTRAINT_FAILED 31alter table t1 add c int default 0 check (c < 10); 32 33set check_constraint_checks=0; 34alter table t1 add c int default 0 check (c < 10); 35alter table t1 add check (a+b+c < 500); 36set check_constraint_checks=@save_check_constraint; 37 38show create table t1; 39--error ER_CONSTRAINT_FAILED 40insert into t1 values(105,105,105); 41--error ER_CONSTRAINT_FAILED 42insert into t1 values(249,249,9); 43insert into t1 values(105,105,9); 44select * from t1; 45 46create table t2 like t1; 47show create table t2; 48--error ER_CANT_DROP_FIELD_OR_KEY 49alter table t2 drop constraint c; 50alter table t2 drop constraint if exists c; 51alter table t2 drop constraint min; 52show create table t2; 53 54drop table t1,t2; 55 56# 57# check constraint name auto-generation: 58# 59create or replace table t1 (a int, b int, constraint check (a>b)); 60show create table t1; 61create or replace table t1 (a int, b int, 62 constraint CONSTRAINT_1 check (a>1), 63 constraint check (b>1)); 64show create table t1; 65create or replace table t1 (a int, b int, 66 constraint CONSTRAINT_1 check (a>1), 67 constraint check (b>1), 68 constraint CONSTRAINT_2 check (a>b)); 69show create table t1; 70drop table t1; 71 72# 73# MDEV-10370 Check constraints on virtual columns fails on INSERT when column not specified 74# 75create table t1(c1 int, c2 int as (c1 + 1), check (c2 > 2)); 76--error ER_CONSTRAINT_FAILED 77insert into t1(c1) values(1); 78insert into t1(c1) values(2); 79drop table t1; 80 81# 82# MDEV-11117 CHECK constraint fails on intermediate step of ALTER 83# 84-- error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 85create or replace table t1( c1 int auto_increment primary key, check( c1 > 0 or c1 is null ) ); 86 87# 88# MDEV-12421 Check constraint with query crashes server and renders DB unusable 89# 90--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 91create table t1 (a int check (@b in (select user from mysql.user))); 92--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 93create table t1 (a int check (a > @b)); 94 95# 96# MDEV-13596 CHECK constraints disallow NULL to pass through, violating SQL 97# 98create table t1 (a int check (a = 1)); 99insert t1 values (1); 100--error ER_CONSTRAINT_FAILED 101insert t1 values (2); 102insert t1 values (NULL); 103select * from t1; 104drop table t1; 105 106# 107# MDEV-15141 Check constraint validation on a datetime field crashes the process 108# 109create table t1 (id int auto_increment primary key, datecol datetime, check (datecol>'0001-01-01 00:00:00')); 110insert into t1 (datecol) values (now()); 111insert into t1 (datecol) values (now()); 112drop table t1; 113 114# 115# MDEV-15461 Check Constraints with binary logging makes insert inconsistent 116# 117 118CREATE TABLE t1 ( 119 EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 120 FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) 121); 122 123--error ER_TRUNCATED_WRONG_VALUE 124INSERT INTO t1 VALUES (NULL, 'Ken'); 125SHOW WARNINGS; 126--error ER_TRUNCATED_WRONG_VALUE 127INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); 128SHOW WARNINGS; 129INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'); 130INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); 131set sql_mode=""; 132INSERT INTO t1 VALUES (NULL, 'Ken'); 133INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); 134set sql_mode=default; 135select * from t1; 136drop table t1; 137 138--echo # 139--echo # MDEV-16630: Ambiguous error message when check constraint 140--echo # matches table name 141--echo # 142 143use test; 144--disable_warnings 145drop table if exists t; 146--enable_warnings 147 148create table t(a int, b int check(b>0), 149 constraint b check(a<b), constraint a check(a>0), 150 constraint x check (a>10)); 151 152show create table t; 153 154# Generate error when field constraint 'b' is violated 155--echo # Field constraint 'b' will fail 156--error ER_CONSTRAINT_FAILED 157insert into t values (-1, 0); 158 159# Generate error when table constraint 'b' is violated. 160--echo # Table constraint 'b' will fail 161--error ER_CONSTRAINT_FAILED 162insert into t values (1,1); 163 164drop table t; 165 166# 167# check constraints and auto_is_null typo 168# 169create table t1 (a int auto_increment primary key, b int, check (b > 5)); 170--error ER_CONSTRAINT_FAILED 171insert t1 (b) values (1); 172insert t1 (b) values (10); 173select * from t1 where a is null; 174set sql_auto_is_null=1; 175select * from t1 where a is null; 176--error ER_CONSTRAINT_FAILED 177insert t1 (b) values (1); 178drop table t1; 179 180--echo # 181--echo # MDEV-24274 ALTER TABLE with CHECK CONSTRAINTS gives "Out of Memory" error 182--echo # 183create table t1 (id varchar(2), constraint id check (id regexp '[a-z]')); 184alter table t1 force; 185show create table t1; 186drop table t1; 187