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