242 lines
9.8 KiB
Plaintext
242 lines
9.8 KiB
Plaintext
create table parent (
|
|
a int primary key,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
create table child (
|
|
a int primary key,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
alter table child add constraint fk1 foreign key (a) references parent(a);
|
|
alter table child add constraint fk2 foreign key (b) references parent(a);
|
|
alter table child 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,
|
|
`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 add constraint fk4 foreign key (a) references parent(b);
|
|
alter table child add constraint fk5 foreign key (b) references parent(b);
|
|
alter table child add constraint fk6 foreign key (c) references parent(b);
|
|
show create table child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a` int(11) NOT NULL,
|
|
`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,
|
|
CONSTRAINT `fk4` FOREIGN KEY (`a`) REFERENCES `parent` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `parent` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk6` FOREIGN KEY (`c`) REFERENCES `parent` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table child algorithm=copy, add constraint fk7 foreign key (a) references parent(c);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk7' in the referenced table 'parent'
|
|
alter table child algorithm=copy, add constraint fk8 foreign key (a) references parent(c);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk8' in the referenced table 'parent'
|
|
alter table child algorithm=copy, add constraint fk9 foreign key (a) references parent(c);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk9' in the referenced table 'parent'
|
|
alter table child algorithm=inplace, add constraint fk7 foreign key (a) references parent(c);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk7' in the referenced table 'parent'
|
|
alter table child algorithm=inplace, add constraint fk8 foreign key (a) references parent(c);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk8' in the referenced table 'parent'
|
|
alter table child algorithm=inplace, add constraint fk9 foreign key (a) references parent(c);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk9' in the referenced table 'parent'
|
|
drop table child, parent;
|
|
create table parent (
|
|
a int primary key,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
create table child (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key (a),
|
|
unique key (b) using hash,
|
|
key (c),
|
|
constraint fk1 foreign key(a) references parent (a),
|
|
constraint fk2 foreign key(b) references parent (a),
|
|
constraint fk3 foreign key(c) references parent (a),
|
|
constraint fk4 foreign key(a) references parent (b),
|
|
constraint fk5 foreign key(b) references parent (b),
|
|
constraint fk6 foreign key(c) references parent (b)
|
|
) engine=ndbcluster;
|
|
show create table child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a` int(11) NOT NULL,
|
|
`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,
|
|
CONSTRAINT `fk4` FOREIGN KEY (`a`) REFERENCES `parent` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `parent` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk6` FOREIGN KEY (`c`) REFERENCES `parent` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table child, parent;
|
|
#
|
|
# Bug#24666177 : PARENT TABLE'S HANDLER NOT UPDATED AFTER ADDING A FK DEPENDENCY
|
|
#
|
|
#Setup tables
|
|
create table parent(
|
|
id int unique key
|
|
) engine ndb;
|
|
create table child(
|
|
ref int
|
|
) engine ndb;
|
|
insert into parent values(1), (2), (3);
|
|
insert into child values(1), (2), (3);
|
|
#Show create table on parent.
|
|
#This will now load the m_fk_data of parent on handler
|
|
show create table parent;
|
|
Table Create Table
|
|
parent CREATE TABLE `parent` (
|
|
`id` int(11) DEFAULT NULL,
|
|
UNIQUE KEY `id` (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#Now create a foreign key dependency
|
|
alter table child add constraint fk1 foreign key (ref) references parent(id);
|
|
#Now run a alter engine query.
|
|
#m_fk_data of parent should be reloaded now and the query should fail
|
|
alter table parent engine = innodb;
|
|
ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
|
|
show create table parent;
|
|
Table Create Table
|
|
parent CREATE TABLE `parent` (
|
|
`id` int(11) DEFAULT NULL,
|
|
UNIQUE KEY `id` (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#Repeat same with truncate
|
|
truncate table child;
|
|
alter table parent engine = innodb;
|
|
ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
|
|
show create table parent;
|
|
Table Create Table
|
|
parent CREATE TABLE `parent` (
|
|
`id` int(11) DEFAULT NULL,
|
|
UNIQUE KEY `id` (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#Repeat same test for dropping FK
|
|
alter table child drop foreign key fk1;
|
|
# Now this should succeed
|
|
alter table parent engine = innodb;
|
|
show create table parent;
|
|
Table Create Table
|
|
parent CREATE TABLE `parent` (
|
|
`id` int(11) DEFAULT NULL,
|
|
UNIQUE KEY `id` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#Cleanup
|
|
drop table parent, child;
|
|
#
|
|
# Bug#25859977 : WRONG ERROR MESSAGE SHOWN AFTER ALTER ADD FK FAILURE
|
|
#
|
|
# Setup
|
|
create table parent (
|
|
a int unique,
|
|
b int,
|
|
c int,
|
|
key b_key(b),
|
|
key c_key(c),
|
|
constraint fk_p1 foreign key (b) references parent(a)
|
|
) engine ndb;
|
|
create table child (
|
|
a int,
|
|
b int,
|
|
key a_key(a),
|
|
key b_key(b),
|
|
constraint fk_c1 foreign key (a) references parent(a)
|
|
) engine ndb;
|
|
# Try creating fk with already exisiting names. Should fail.
|
|
alter table parent add constraint fk_p1 foreign key (c) references parent(a);
|
|
ERROR HY000: Duplicate foreign key constraint name 'fk_p1'
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1826 Duplicate foreign key constraint name 'fk_p1'
|
|
alter table child add constraint fk_c1 foreign key (b) references parent(a);
|
|
ERROR HY000: Duplicate foreign key constraint name 'fk_c1'
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1826 Duplicate foreign key constraint name 'fk_c1'
|
|
# Check if error is thrown when creating table
|
|
create table test_fail (
|
|
a int,
|
|
b int,
|
|
key a_key(a),
|
|
key b_key(b),
|
|
constraint fk_t1 foreign key (a) references parent(a),
|
|
constraint fk_t1 foreign key (b) references parent(a)
|
|
) engine ndb;
|
|
ERROR HY000: Duplicate foreign key constraint name 'fk_t1'
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1826 Duplicate foreign key constraint name 'fk_t1'
|
|
# Using distinct names should work
|
|
alter table parent add constraint fk_p2 foreign key (b) references parent(a);
|
|
alter table parent add constraint fk_p3 foreign key (c) references parent(a);
|
|
alter table child add constraint fk_c2 foreign key (b) references parent(a);
|
|
#Show create tables
|
|
show create table parent;
|
|
Table Create Table
|
|
parent CREATE TABLE `parent` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
UNIQUE KEY `a` (`a`),
|
|
KEY `b_key` (`b`),
|
|
KEY `c_key` (`c`),
|
|
CONSTRAINT `fk_p1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk_p2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk_p3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
show create table child;
|
|
Table Create Table
|
|
child CREATE TABLE `child` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
KEY `a_key` (`a`),
|
|
KEY `b_key` (`b`),
|
|
CONSTRAINT `fk_c1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk_c2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
# Cleanup
|
|
drop table child, parent;
|
|
#
|
|
# Bug#25882950 : CACHED INDEX IN NDBAPI'S DICT CACHE NOT CLEARED AFTER `CREATE TABLE` FAILURE
|
|
#
|
|
# Create a table with bad fk reference
|
|
create table test(
|
|
a int,
|
|
foreign key fk1(a) references bogus_table(a)
|
|
) engine ndb;
|
|
ERROR HY000: Failed to open the referenced table 'bogus_table'
|
|
# Now create a table with proper reference - it should pass
|
|
create table test (
|
|
a int unique,
|
|
b int,
|
|
foreign key fk1(b) references test(a)
|
|
) engine ndb;
|
|
#Cleanup
|
|
drop table test;
|