1# 2# Testing of constraints 3# 4create table t1 (a int check (a>0)); 5show create table t1; 6insert into t1 values (1); 7--error ER_CONSTRAINT_FAILED 8insert into t1 values (0); 9drop table t1; 10create table t1 (a int, b int, check (a>b)); 11show create table t1; 12insert into t1 values (1,0); 13--error ER_CONSTRAINT_FAILED 14insert into t1 values (0,1); 15drop table t1; 16create table t1 (a int ,b int, constraint abc check (a>b)); 17show create table t1; 18insert into t1 values (1,0); 19--error ER_CONSTRAINT_FAILED 20insert into t1 values (0,1); 21drop table t1; 22create table t1 (a int null); 23show create table t1; 24insert into t1 values (1),(NULL); 25drop table t1; 26create table t1 (a int null); 27alter table t1 add constraint constraint_1 unique (a); 28alter table t1 add constraint unique key_1(a); 29alter table t1 add constraint constraint_2 unique key_2(a); 30show create table t1; 31drop table t1; 32 33# End of 4.1 tests 34 35# 36# Bug#35578 (Parser allows useless/illegal CREATE TABLE syntax) 37# 38 39--disable_warnings 40drop table if exists t_illegal; 41--enable_warnings 42 43--error ER_PARSE_ERROR 44create table t_illegal (a int, b int, check a>b); 45 46--error ER_PARSE_ERROR 47create table t_illegal (a int, b int, constraint abc check a>b); 48 49--error ER_PARSE_ERROR 50create table t_illegal (a int, b int, constraint abc); 51 52# 53# Bug#11714 (Non-sensical ALTER TABLE ADD CONSTRAINT allowed) 54# 55 56--disable_warnings 57drop table if exists t_11714; 58--enable_warnings 59 60create table t_11714(a int, b int); 61 62--error ER_PARSE_ERROR 63alter table t_11714 add constraint cons1; 64 65drop table t_11714; 66 67# 68# Bug#38696 (CREATE TABLE ... CHECK ... allows illegal syntax) 69 70--error ER_PARSE_ERROR 71CREATE TABLE t_illegal (col_1 INT CHECK something (whatever)); 72 73--error ER_PARSE_ERROR 74CREATE TABLE t_illegal (col_1 INT CHECK something); 75 76# 77# MDEV-17065 Crash on SHOW CREATE TABLE with CHECK CONSTRAINT 78# 79 80CREATE TABLE long_enough_name ( 81pk int(11) NOT NULL, 82f1 int(11) DEFAULT NULL, 83f2 int(11) NOT NULL, 84f3 int(11) DEFAULT NULL, 85f4 timestamp NOT NULL DEFAULT current_timestamp(), 86f5 varchar(32) COLLATE utf8_bin NOT NULL DEFAULT 'foo', 87f6 smallint(6) NOT NULL DEFAULT 1, 88f7 int(11) DEFAULT NULL, 89PRIMARY KEY (pk), 90KEY idx1 (f7), 91KEY idx2 (f1), 92KEY idx3 (f2), 93KEY idx4 (f3), 94CONSTRAINT constr CHECK (f6 >= 0) 95); 96 97SELECT * FROM long_enough_name AS tbl; 98SHOW CREATE TABLE long_enough_name; 99 100DROP TABLE long_enough_name; 101 102# 103# MDEV-17654 Incorrect syntax returned for column with CHECK constraint 104# in the "SHOW CREATE TABLE ..." result 105# 106 107CREATE TABLE test.t(t int COMMENT 't_comment' CHECK(t>0)); 108SHOW CREATE TABLE test.t; 109DROP table test.t; 110 111SET @OLD_SQL_MODE=@@SQL_MODE; 112SET SQL_MODE='IGNORE_BAD_TABLE_OPTIONS'; 113 114CREATE TABLE test.t (f int foo=bar check(f>0)); 115SHOW CREATE TABLE t; 116DROP table test.t; 117SET @@SQL_MODE=@OLD_SQL_MODE; 118 119--echo # 120--echo # MDEV-16932 - ASAN heap-use-after-free in my_charlen_utf8 / 121--echo # my_well_formed_char_length_utf8 on 2nd execution of SP with 122--echo # ALTER trying to add bad CHECK 123--echo # 124 125CREATE TABLE t1 (a INT); 126CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); 127--error ER_BAD_FIELD_ERROR 128CALL sp; 129--error ER_BAD_FIELD_ERROR 130CALL sp; 131--error ER_BAD_FIELD_ERROR 132CALL sp; 133show create table t1; 134alter table t1 add column b int; 135CALL sp; 136show create table t1; 137CALL sp; 138show create table t1; 139# Cleanup 140DROP PROCEDURE sp; 141DROP TABLE t1; 142 143CREATE TABLE t1 (a INT); 144CREATE PROCEDURE sp() ALTER TABLE t1 ADD CONSTRAINT CHECK (b > 0); 145--error ER_BAD_FIELD_ERROR 146CALL sp; 147alter table t1 add column b int, add constraint check (b < 10); 148CALL sp; 149show create table t1; 150# Cleanup 151DROP PROCEDURE sp; 152DROP TABLE t1; 153 154--echo # End of 10.2 tests 155 156# 157# Check that we don't lose constraints as part of CREATE ... SELECT 158# 159 160create table t1 (a int check (a>10)) select 100 as 'a'; 161show create table t1; 162drop table t1; 163 164# 165# Check that we constraints on field with default expressions work 166# 167 168create table t1 (a text default(length(now())) check (length(a) > 1)); 169insert into t1 values (); 170insert into t1 values ("ccc"); 171--error ER_CONSTRAINT_FAILED 172insert into t1 values (""); 173select * from t1; 174drop table t1; 175