blob: b39abdc1b2443604c521b8eede2013be2b406cee (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
|
--source include/have_innodb.inc
--echo #
--echo # MDEV-14474: Create INFORMATION_SCHEMA.CHECK_CONSTRAINTS
--echo #
set check_constraint_checks=1;
use test;
create table t0
(
t int, check (t>32) # table constraint
) ENGINE=myisam;
--vertical_results
SELECT * from information_schema.check_constraints order by check_clause;
ALTER TABLE t0
ADD CONSTRAINT CHK_t0_t CHECK(t<100);
SELECT * from information_schema.check_constraints order by check_clause;
ALTER TABLE t0
DROP CONSTRAINT CHK_t0_t;
SELECT * from information_schema.check_constraints order by check_clause;
CREATE TABLE t1
( t int CHECK(t>2), # field constraint
tt int, CONSTRAINT CHK_tt CHECK(tt<100) # table constraint
) ENGINE=InnoDB;
SELECT * from information_schema.check_constraints order by check_clause;
ALTER TABLE t1
DROP CONSTRAINT CHK_tt;
SELECT * from information_schema.check_constraints order by check_clause;
create table t2
(
name VARCHAR(30) CHECK(CHAR_LENGTH(name)>2), #field constraint
start_date DATE,
end_date DATE,
CONSTRAINT CHK_dates CHECK(start_date IS NULL) #table constraint
)ENGINE=Innodb;
SELECT * from information_schema.check_constraints order by check_clause;
ALTER TABLE t1
ADD CONSTRAINT CHK_new_ CHECK(t>tt);
SELECT * from information_schema.check_constraints order by check_clause;
# Create table with same field and table check constraint name
create table t3
(
a int,
b int check (b>0), # field constraint named 'b'
CONSTRAINT b check (b>10) # table constraint
) ENGINE=InnoDB;
--horizontal_results
select * from information_schema.check_constraints order by check_clause;
drop table t0;
drop table t1;
drop table t2;
drop table t3;
|
