292 lines
10 KiB
Plaintext
292 lines
10 KiB
Plaintext
create table parent (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
create table child (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
alter table child algorithm=inplace, add constraint fk1 foreign key (a) references parent(a);
|
|
alter table child algorithm=inplace, add constraint fk2 foreign key (b) references parent(a);
|
|
alter table child algorithm=inplace, add constraint fk3 foreign key (c) references parent(a);
|
|
show create table child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `b` (`b`) USING HASH,
|
|
KEY `c` (`c`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table child algorithm=inplace, drop foreign key fk1;
|
|
show create table child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `b` (`b`) USING HASH,
|
|
KEY `c` (`c`),
|
|
CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table child algorithm=inplace, drop foreign key fk2;
|
|
show create table child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `b` (`b`) USING HASH,
|
|
KEY `c` (`c`),
|
|
CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table child algorithm=inplace, drop foreign key fk3;
|
|
show create table child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `b` (`b`) USING HASH,
|
|
KEY `c` (`c`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table parent, child;
|
|
CREATE TABLE bug46599a (
|
|
b VARCHAR(2), PRIMARY KEY(b)) ENGINE=ndb;
|
|
CREATE TABLE bug46599b (b VARCHAR(2),
|
|
CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES bug46599a (b)) ENGINE=Ndb;
|
|
INSERT INTO bug46599a VALUES ('b');
|
|
INSERT INTO bug46599b VALUES ('b'), (NULL);
|
|
INSERT INTO bug46599b VALUES ('');
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`bug46599b`, CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `bug46599a` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
SELECT * FROM bug46599a;
|
|
b
|
|
b
|
|
SELECT * FROM bug46599b;
|
|
b
|
|
NULL
|
|
b
|
|
ALTER TABLE bug46599b MODIFY COLUMN b VARCHAR(2) NOT NULL DEFAULT '';
|
|
ERROR HY000: Cannot change column 'b': used in a foreign key constraint 'fk1'
|
|
SELECT * FROM bug46599b;
|
|
b
|
|
NULL
|
|
b
|
|
DROP TABLE bug46599b,bug46599a;
|
|
create table t1(
|
|
col1 int not null primary key,
|
|
col2 int not null,
|
|
col3 varchar(35) not null,
|
|
unique name0 (col2),
|
|
unique name1 (col3)
|
|
) engine=ndb;
|
|
create table t2(
|
|
col1 int not null unique,
|
|
col2 int not null,
|
|
col3 varchar(35),
|
|
constraint fk1 foreign key (col3) references t1(col3)
|
|
) engine=ndb;
|
|
ALTER TABLE t1 DROP COLUMN col2;
|
|
drop table t2, t1;
|
|
set @i=2;
|
|
create table t1(
|
|
col1 int not null primary key,
|
|
col2 int not null unique,
|
|
col3 varchar(35)
|
|
) engine=innodb;
|
|
create table t2(
|
|
col1 int not null unique,
|
|
col2 int not null,
|
|
col3 varchar(35),
|
|
constraint fk1 foreign key (col2) references t1(col1)
|
|
) engine=innodb;
|
|
insert into t1(col1,col2,col3) values (2, 3, 'abcdefghijkl');
|
|
insert into t2(col1,col2,col3) values (1, 2, 'abcdefghijkl');
|
|
# Attempt to drop col1, should give error
|
|
ALTER TABLE t1 DROP COLUMN col1;
|
|
ERROR HY000: Cannot drop column 'col1': needed in a foreign key constraint 'fk1' of table 't2'
|
|
# Attempt to modify col1, should give error
|
|
ALTER TABLE t1 MODIFY COLUMN col1 bigint;
|
|
ERROR HY000: Referencing column 'col2' and referenced column 'col1' in foreign key constraint 'fk1' are incompatible.
|
|
# Drop the column t1.col3
|
|
ALTER TABLE t1 DROP COLUMN col3;
|
|
# Drop t2.col2
|
|
ALTER TABLE t2 DROP COLUMN col2;
|
|
Got one of the listed errors
|
|
drop table t2, t1;
|
|
set @i=1;
|
|
create table t1(
|
|
col1 int not null primary key,
|
|
col2 int not null unique,
|
|
col3 varchar(35)
|
|
) engine=ndb;
|
|
create table t2(
|
|
col1 int not null unique,
|
|
col2 int not null,
|
|
col3 varchar(35),
|
|
constraint fk1 foreign key (col2) references t1(col1)
|
|
) engine=ndb;
|
|
insert into t1(col1,col2,col3) values (2, 3, 'abcdefghijkl');
|
|
insert into t2(col1,col2,col3) values (1, 2, 'abcdefghijkl');
|
|
# Attempt to drop col1, should give error
|
|
ALTER TABLE t1 DROP COLUMN col1;
|
|
ERROR HY000: Cannot drop column 'col1': needed in a foreign key constraint 'fk1' of table 't2'
|
|
# Attempt to modify col1, should give error
|
|
ALTER TABLE t1 MODIFY COLUMN col1 bigint;
|
|
ERROR HY000: Referencing column 'col2' and referenced column 'col1' in foreign key constraint 'fk1' are incompatible.
|
|
# Drop the column t1.col3
|
|
ALTER TABLE t1 DROP COLUMN col3;
|
|
# Drop t2.col2
|
|
ALTER TABLE t2 DROP COLUMN col2;
|
|
Got one of the listed errors
|
|
drop table t2, t1;
|
|
#
|
|
# Test non-auto partitioning
|
|
#
|
|
CREATE TABLE t1
|
|
(col1 int NOT NULL PRIMARY KEY,
|
|
col2 int NOT NULL,
|
|
col3 varchar(35) NOT NULL,
|
|
UNIQUE KEY name0 (col2),
|
|
UNIQUE KEY name1 (col3)
|
|
)
|
|
ENGINE = NDB;
|
|
CREATE TABLE t2
|
|
(col1 int NOT NULL UNIQUE,
|
|
col2 int NOT NULL,
|
|
col3 varchar(35),
|
|
CONSTRAINT fk1 FOREIGN KEY (col3) REFERENCES t1 (col3)
|
|
)
|
|
ENGINE = NDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) NOT NULL,
|
|
PRIMARY KEY (`col1`),
|
|
UNIQUE KEY `name0` (`col2`),
|
|
UNIQUE KEY `name1` (`col3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) DEFAULT NULL,
|
|
UNIQUE KEY `col1` (`col1`),
|
|
KEY `fk1` (`col3`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
ALTER TABLE t1 PARTITION BY KEY ();
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) NOT NULL,
|
|
PRIMARY KEY (`col1`),
|
|
UNIQUE KEY `name0` (`col2`),
|
|
UNIQUE KEY `name1` (`col3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY () */
|
|
ALTER TABLE t1 PARTITION BY KEY (col1);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) NOT NULL,
|
|
PRIMARY KEY (`col1`),
|
|
UNIQUE KEY `name0` (`col2`),
|
|
UNIQUE KEY `name1` (`col3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (col1) */
|
|
ALTER TABLE t1 REMOVE PARTITIONING;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) NOT NULL,
|
|
PRIMARY KEY (`col1`),
|
|
UNIQUE KEY `name0` (`col2`),
|
|
UNIQUE KEY `name1` (`col3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
ALTER TABLE t2 PARTITION BY KEY ();
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) DEFAULT NULL,
|
|
UNIQUE KEY `col1` (`col1`),
|
|
KEY `fk1` (`col3`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY () */
|
|
ALTER TABLE t2 PARTITION BY KEY (col1);
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) DEFAULT NULL,
|
|
UNIQUE KEY `col1` (`col1`),
|
|
KEY `fk1` (`col3`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY KEY (col1) */
|
|
ALTER TABLE t2 REMOVE PARTITIONING;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) DEFAULT NULL,
|
|
UNIQUE KEY `col1` (`col1`),
|
|
KEY `fk1` (`col3`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
ALTER TABLE t1 RENAME COLUMN col3 TO col3_new;
|
|
ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
|
|
ALTER TABLE t1 ALGORITHM=inplace, RENAME COLUMN col3 TO col3_new;
|
|
ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
|
|
ALTER TABLE t2 RENAME COLUMN col3 TO col3_new;
|
|
ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
|
|
ALTER TABLE t2 ALGORITHM=inplace, RENAME COLUMN col3 TO col3_new;
|
|
ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) NOT NULL,
|
|
PRIMARY KEY (`col1`),
|
|
UNIQUE KEY `name0` (`col2`),
|
|
UNIQUE KEY `name1` (`col3`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`col1` int(11) NOT NULL,
|
|
`col2` int(11) NOT NULL,
|
|
`col3` varchar(35) DEFAULT NULL,
|
|
UNIQUE KEY `col1` (`col1`),
|
|
KEY `fk1` (`col3`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t2, t1;
|