1-- source include/have_ndb.inc 2 3### 4### PK vs PK 5### 6create table parent ( 7 a int primary key auto_increment, 8 b int not null, 9 c int not null, 10 unique(b) using hash, 11 index(c)) engine = ndb; 12 13create table child ( 14 a int primary key auto_increment, 15 b int not null, 16 c int not null, 17 unique(b) using hash, 18 index(c)) engine = ndb; 19 20# Parent pk 21# child pk, uk, oi 22alter table child algorithm=inplace, add constraint fk1 foreign key (a) references parent(a); 23alter table child algorithm=inplace, add constraint fk2 foreign key (b) references parent(a); 24alter table child algorithm=inplace, add constraint fk3 foreign key (c) references parent(a); 25 26show create table child; 27 28alter table child algorithm=inplace, drop foreign key fk1; 29 30show create table child; 31 32alter table child algorithm=inplace, drop foreign key fk2; 33 34show create table child; 35 36alter table child algorithm=inplace, drop foreign key fk3; 37 38show create table child; 39 40drop table parent, child; 41 42# 43# 44# 45CREATE TABLE bug46599a ( 46 b VARCHAR(2), PRIMARY KEY(b)) ENGINE=ndb; 47 48CREATE TABLE bug46599b (b VARCHAR(2), 49 CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES bug46599a (b)) ENGINE=Ndb; 50 51INSERT INTO bug46599a VALUES ('b'); 52INSERT INTO bug46599b VALUES ('b'), (NULL); 53--error ER_NO_REFERENCED_ROW_2 54INSERT INTO bug46599b VALUES (''); 55 56--sorted_result 57SELECT * FROM bug46599a; 58--sorted_result 59SELECT * FROM bug46599b; 60 61--error ER_FK_COLUMN_CANNOT_CHANGE 62ALTER TABLE bug46599b MODIFY COLUMN b VARCHAR(2) NOT NULL DEFAULT ''; 63 64--sorted_result 65SELECT * FROM bug46599b; 66 67DROP TABLE bug46599b,bug46599a; 68 69# 70# bug#16912989 - part I 71# 72# copy_fk_for_offline_alter must use column name (not column number) 73# in determining columns for newly created FK's 74# This as column number change with DROP COLUMN 75# 76create table t1( 77 col1 int not null primary key, 78 col2 int not null, 79 col3 varchar(35) not null, 80 unique name0 (col2), 81 unique name1 (col3) 82) engine=ndb; 83 84create table t2( 85 col1 int not null unique, 86 col2 int not null, 87 col3 varchar(35), 88 constraint fk1 foreign key (col3) references t1(col3) 89) engine=ndb; 90 91ALTER TABLE t1 DROP COLUMN col2; 92 93drop table t2, t1; 94 95# 96# bug#16912989 - part II 97# 98# When offline altering a parent table, the on the child recreated FK's 99# can't be verified since the copying of data to the new parent 100# is done later 101# 102let $i=2; 103while ($i) 104{ 105 eval set @i=$i; 106 let $engine=`select if(@i=1,'ndb','innodb')`; 107 108 eval create table t1( 109 col1 int not null primary key, 110 col2 int not null unique, 111 col3 varchar(35) 112 ) engine=$engine; 113 114 eval create table t2( 115 col1 int not null unique, 116 col2 int not null, 117 col3 varchar(35), 118 constraint fk1 foreign key (col2) references t1(col1) 119 ) engine=$engine; 120 121 insert into t1(col1,col2,col3) values (2, 3, 'abcdefghijkl'); 122 insert into t2(col1,col2,col3) values (1, 2, 'abcdefghijkl'); 123 124 --echo # Attempt to drop col1, should give error 125 --error 1829 126 ALTER TABLE t1 DROP COLUMN col1; 127 128 --echo # Attempt to modify col1, should give error 129 --error 1833 130 ALTER TABLE t1 MODIFY COLUMN col1 bigint; 131 132 --echo # Drop the column t1.col3 133 ALTER TABLE t1 DROP COLUMN col3; 134 135 --echo # Drop t2.col2 136 --error 1553, 1828 137 ALTER TABLE t2 DROP COLUMN col2; 138 139 drop table t2, t1; 140 dec $i; 141} 142 143--echo # 144--echo # Test non-auto partitioning 145--echo # 146CREATE TABLE t1 147(col1 int NOT NULL PRIMARY KEY, 148 col2 int NOT NULL, 149 col3 varchar(35) NOT NULL, 150 UNIQUE KEY name0 (col2), 151 UNIQUE KEY name1 (col3) 152) 153ENGINE = NDB; 154 155CREATE TABLE t2 156(col1 int NOT NULL UNIQUE, 157 col2 int NOT NULL, 158 col3 varchar(35), 159 CONSTRAINT fk1 FOREIGN KEY (col3) REFERENCES t1 (col3) 160) 161ENGINE = NDB; 162 163SHOW CREATE TABLE t1; 164SHOW CREATE TABLE t2; 165ALTER TABLE t1 PARTITION BY KEY (); 166SHOW CREATE TABLE t1; 167ALTER TABLE t1 PARTITION BY KEY (col1); 168SHOW CREATE TABLE t1; 169ALTER TABLE t1 REMOVE PARTITIONING; 170SHOW CREATE TABLE t1; 171 172ALTER TABLE t2 PARTITION BY KEY (); 173SHOW CREATE TABLE t2; 174ALTER TABLE t2 PARTITION BY KEY (col1); 175SHOW CREATE TABLE t2; 176ALTER TABLE t2 REMOVE PARTITIONING; 177SHOW CREATE TABLE t2; 178 179DROP TABLE t2, t1; 180 181