Debian Codegraph @

mysql-test/suite/ndb/r/ndb_alter_table3.result

raw
1 DROP TABLE IF EXISTS t1;
2 create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) )
3 engine=ndb;
4 insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three');
5 create index c on t1(c);
6 show indexes from t1;
7 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
8 t1 0 PRIMARY 1 a A 3 NULL NULL BTREE YES NULL
9 t1 1 b 1 b A NULL NULL NULL YES BTREE YES NULL
10 t1 1 c 1 c A NULL NULL NULL YES BTREE YES NULL
11 select * from t1 where c = 'two';
12 a b c
13 2 two two
14 alter table t1 drop index c;
15 show indexes from t1;
16 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
17 t1 0 PRIMARY 1 a A 3 NULL NULL BTREE YES NULL
18 t1 1 b 1 b A NULL NULL NULL YES BTREE YES NULL
19 select * from t1 where c = 'two';
20 a b c
21 2 two two
22 drop table t1;
23 create table t3 (a int primary key) engine=ndbcluster;
24 insert into t3 values (1);
25 alter table t3 rename t4;
26 select * from t3;
27 ERROR 42S02: Table 'test.t3' doesn't exist
28 select * from t4;
29 a
30 1
31 drop table t4;
32 show tables;
33 Tables_in_test
34 drop table if exists t6;
35 create table t6 (a int primary key ,b int) engine=ndbcluster;
36 insert into t6 values(1,12), (2,12);
37 alter table t6 add unique key ui_t(b);
38 ERROR 23000: Can't write, because of unique constraint, to table 't6'
39 delete from t6;
40 insert into t6 values(1,12), (2,12);
41 alter table t6 add unique key ui_t(b);
42 ERROR 23000: Can't write, because of unique constraint, to table 't6'
43 delete from t6;
44 drop table t6;
45 #
46 # Bug#22173891 : CIRCULAR FOREIGN KEYS + INDEX = FAILURE
47 #
48 #create table t1
49 create table t1(
50 a int primary key,
51 b int unique key
52 )engine = ndb;
53 #create table t2 with fks referring columns from t1
54 create table t2(
55 a int,
56 b int,
57 c int unique key,
58 constraint fk1 foreign key (a) references t1(a),
59 constraint fk2 foreign key (b) references t1(b)
60 )engine = ndb;
61 #t2 now has additional keys on col `a` and `b` to support fks.
62 show create table t2;
63 Table Create Table
64 t2 CREATE TABLE `t2` (
65 `a` int DEFAULT NULL,
66 `b` int DEFAULT NULL,
67 `c` int DEFAULT NULL,
68 UNIQUE KEY `c` (`c`),
69 KEY `fk1` (`a`),
70 KEY `fk2` (`b`),
71 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`),
72 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`b`)
73 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
74 #create an index on t2(a) using 'create index'
75 create index id1 on t2(a);
76 #verify that the implicit key on col `a` is gone now and fk is intact.
77 show create table t2;
78 Table Create Table
79 t2 CREATE TABLE `t2` (
80 `a` int DEFAULT NULL,
81 `b` int DEFAULT NULL,
82 `c` int DEFAULT NULL,
83 UNIQUE KEY `c` (`c`),
84 KEY `fk2` (`b`),
85 KEY `id1` (`a`),
86 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`),
87 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`b`)
88 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
89 insert into t1 values (1,10), (2,20), (3,30), (4,40);
90 insert into t2 values (1,10,100), (2,20,200);
91 insert into t2 values (5,50,500);
92 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_name` FOREIGN KEY (`col_name`) REFERENCES `t1` (`col_name`) ON DELETE NO ACTION ON UPDATE NO ACTION)
93 #create a table t3 with an fk refering to t2(c)
94 create table t3(
95 a int,
96 constraint fk3 foreign key (a) references t2(c)
97 )engine = ndb;
98 #create an index on col `b` on t2
99 create unique index id2 on t2(b);
100 #verify that the implicit key on col `b` is also gone now.
101 show create table t2;
102 Table Create Table
103 t2 CREATE TABLE `t2` (
104 `a` int DEFAULT NULL,
105 `b` int DEFAULT NULL,
106 `c` int DEFAULT NULL,
107 UNIQUE KEY `c` (`c`),
108 UNIQUE KEY `id2` (`b`),
109 KEY `id1` (`a`),
110 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`),
111 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`b`)
112 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
113 #verify that the fks are intact in t2 and t3
114 insert into t2 values (3,30,300), (4,40,400);
115 insert into t2 values (5,50,500);
116 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_name` FOREIGN KEY (`col_name`) REFERENCES `t1` (`col_name`) ON DELETE NO ACTION ON UPDATE NO ACTION)
117 insert into t3 values (100), (200);
118 insert into t3 values (500);
119 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `fk3` FOREIGN KEY (`a`) REFERENCES `t2` (`c`) ON DELETE NO ACTION ON UPDATE NO ACTION)
120 #cleanup
121 drop table t3, t2, t1;