915 lines
31 KiB
Plaintext
915 lines
31 KiB
Plaintext
drop table if exists t2, t1;
|
|
# bug#14095785
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2
|
|
add constraint foreign key (b) references t1 (b);
|
|
alter table t2
|
|
drop foreign key;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
|
|
drop table t2, t1;
|
|
# bug#14095838
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2
|
|
add constraint fk1 foreign key (b) references t1 (a)
|
|
on delete cascade on update cascade;
|
|
ERROR HY000: Cannot add foreign key constraint
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1296 Got error 21000 'Create foreign key failed - parent key is primary key and on-update-cascade is not allowed' from NDB
|
|
Error 1215 Cannot add foreign key constraint
|
|
alter table t2
|
|
add constraint fk1 foreign key (b) references t1 (a)
|
|
on delete cascade on update restrict;
|
|
drop table t2, t1;
|
|
# bug#14195170
|
|
CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=NDB;
|
|
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
|
|
price DECIMAL, PRIMARY KEY(category, id)) ENGINE=NDB;
|
|
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
|
|
ERROR HY000: Cannot add foreign key constraint
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1296 Got error 21000 'Create foreign key failed - parent key is primary key and on-update-cascade is not allowed' from NDB
|
|
Error 1215 Cannot add foreign key constraint
|
|
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
|
|
ERROR HY000: Cannot add foreign key constraint
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1296 Got error 21000 'Create foreign key failed - parent key is primary key and on-update-cascade is not allowed' from NDB
|
|
Error 1215 Cannot add foreign key constraint
|
|
DROP TABLE customer, product;
|
|
# bug#14124623
|
|
CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY,
|
|
country VARCHAR(30), INDEX county_country_index(country)) ENGINE=ndb;
|
|
INSERT INTO counties VALUES
|
|
('Berkshire','England'),('Shropshire','England'),('Oxfordshire','England'),('Buckinghamshire','England');
|
|
CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county
|
|
VARCHAR(30), INDEX county_county_index (county), CONSTRAINT county_town
|
|
FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE
|
|
RESTRICT) ENGINE=ndb;
|
|
INSERT INTO towns VALUES
|
|
('Maidenhead','Berkshire'),('Reading','Berkshire'),('Shrewsbury','Shropshire')
|
|
,('Oxford','Oxfordshire');
|
|
CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
|
|
country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
|
|
country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
|
|
RESTRICT ON UPDATE RESTRICT) engine=ndb;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'country_language' in the referenced table 'counties'
|
|
CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
|
|
country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
|
|
country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
|
|
RESTRICT ON UPDATE RESTRICT) engine=ndb;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'country_language' in the referenced table 'counties'
|
|
CREATE TABLE languages (id INT NOT NULL PRIMARY KEY) engine=ndb;
|
|
drop table languages, towns, counties;
|
|
# test: child vs parent column types
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b char(20) not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
ERROR HY000: Referencing column 'b' and referenced column 'b' in foreign key constraint 'fk1' are incompatible.
|
|
create table t2 (
|
|
a int not null,
|
|
b char(10) not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
ERROR HY000: Referencing column 'b' and referenced column 'b' in foreign key constraint 'fk1' are incompatible.
|
|
drop table t2, t1;
|
|
# test: parent table not exist
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
ERROR HY000: Failed to open the referenced table 't1'
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
ERROR HY000: Failed to open the referenced table 't1'
|
|
drop table t2;
|
|
# test: parent column not exist
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing column 'b' for constraint 'fk1' in the referenced table 't1'
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing column 'b' for constraint 'fk1' in the referenced table 't1'
|
|
drop table t2, t1;
|
|
# test: parent key no index
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
|
|
drop table t2, t1;
|
|
# test: parent key no unique index
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
|
|
drop table t2, t1;
|
|
# test: child column not exist
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (c) references t1 (b)
|
|
) engine ndb;
|
|
ERROR 42000: Key column 'c' doesn't exist in table
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1072 Key column 'c' doesn't exist in table
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (c) references t1 (b);
|
|
ERROR 42000: Key column 'c' doesn't exist in table
|
|
show warnings;
|
|
Level Code Message
|
|
Error 1072 Key column 'c' doesn't exist in table
|
|
drop table t2, t1;
|
|
# test: child column no index
|
|
# no error - server creates KEY
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
drop table t2;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
drop table t2, t1;
|
|
# test: child vs parent column count
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b, c)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
|
|
drop table t2, t1;
|
|
# bug#16246874
|
|
create table t1 (
|
|
id int primary key
|
|
) engine=ndb;
|
|
create table t2 (
|
|
id int,
|
|
foreign key(id) references t1(id)
|
|
) engine=ndb;
|
|
select * from information_schema.key_column_usage;
|
|
select REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
|
|
from information_schema.key_column_usage
|
|
where TABLE_SCHEMA = 'test' and TABLE_NAME = 't2';
|
|
REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
|
|
test t1 id
|
|
drop table t2, t1;
|
|
# bug#16275509 - case: alter engine from ndb
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine=ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
primary key using hash (a),
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
alter table t1 engine=innodb;
|
|
ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
|
|
alter table t2 engine=innodb;
|
|
ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
|
|
drop table t2, t1;
|
|
create table t1 (
|
|
a int primary key,
|
|
b int,
|
|
key (b),
|
|
foreign key fk1 (b) references t1 (a)
|
|
) engine=ndb;
|
|
alter table t1 engine=innodb;
|
|
ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
|
|
drop table t1;
|
|
# bug#16275509 - case: allow FKs on partitioned
|
|
set new=on;
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb
|
|
partition by hash (a) partitions 5;
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
drop table t2, t1;
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb;
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
alter table t1
|
|
partition by hash (a) partitions 5;
|
|
drop table t2, t1;
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb;
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb
|
|
partition by hash (a) partitions 5;
|
|
drop table t2;
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
alter table t2
|
|
partition by hash (a) partitions 5;
|
|
drop table t2, t1;
|
|
# bug#16275684 - case: alter FK parent column
|
|
create table t1 (
|
|
a int primary key,
|
|
b enum('a','b','c') not null,
|
|
unique key (b)
|
|
) engine=ndb;
|
|
create table t2 (
|
|
a int primary key,
|
|
b enum('a','b','c') not null,
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine=ndb;
|
|
insert into t1 values (1,'a'),(2,'b');
|
|
insert into t2 values (1,'a'),(2,'a'),(3,'b');
|
|
alter table t1 modify column b enum('a','b') not null;
|
|
ERROR HY000: Cannot change column 'b': used in a foreign key constraint 'fk1' of table 'test.t2'
|
|
alter table t2 drop foreign key fk1;
|
|
alter table t1 modify column b enum('a','b') not null;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` enum('a','b') NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `b` (`b`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
select * from t1 order by a;
|
|
a b
|
|
1 a
|
|
2 b
|
|
select * from t2 order by a;
|
|
a b
|
|
1 a
|
|
2 a
|
|
3 b
|
|
drop table t2, t1;
|
|
# bug#16286164 - case: drop when referenced by different table
|
|
set @i=2;
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine=innodb;
|
|
Warnings:
|
|
Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
|
|
create table t2 (
|
|
a int not null,
|
|
b int,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine=innodb;
|
|
Warnings:
|
|
Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
|
|
drop table t1;
|
|
ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 'fk1' on table 't2'.
|
|
drop table t2, t1;
|
|
set @i=1;
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine=ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine=ndb;
|
|
drop table t1;
|
|
ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 'fk1' on table 't2'.
|
|
drop table t2, t1;
|
|
# bug#16286164 - case: drop when referenced by same table
|
|
set @i=2;
|
|
create table t1 (
|
|
a int not null,
|
|
b int,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine=innodb;
|
|
Warnings:
|
|
Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
|
|
drop table t1;
|
|
set @i=1;
|
|
create table t1 (
|
|
a int not null,
|
|
b int,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine=ndb;
|
|
drop table t1;
|
|
# BugXXZ - multi part unique index parent
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (a,b)
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (a,b),
|
|
foreign key fk1 (a,b) references t1 (a,b)
|
|
) engine ndb;
|
|
drop table t2, t1;
|
|
# child-part-key
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
insert into t1 values (1),(2);
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
key xbc (b,c),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine ndb;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`) USING HASH,
|
|
KEY `xbc` (`b`,`c`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
insert into t2 (a,b,c) values (1,1,11),(2,2,21),(3,2,22);
|
|
insert into t2 (a,b,c) values (4,3,33);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
delete from t1 where a = 1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
drop table t2;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
key xbc (b,c),
|
|
constraint fk1 foreign key (c) references t1 (a)
|
|
) engine ndb;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`) USING HASH,
|
|
KEY `xbc` (`b`,`c`),
|
|
KEY `fk1` (`c`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
|
|
insert into t2 (a,c,b) values (4,3,33);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
delete from t1 where a = 1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
drop table t2;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
unique key xbc (b,c) using hash,
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine ndb;
|
|
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the foreign table 't2'
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
unique key xbc (b,c) using hash,
|
|
constraint fk1 foreign key (c) references t1 (a)
|
|
) engine ndb;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`) USING HASH,
|
|
UNIQUE KEY `xbc` (`b`,`c`) USING HASH,
|
|
KEY `fk1` (`c`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
|
|
insert into t2 (a,c,b) values (4,3,33);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
delete from t1 where a = 1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
drop table t2, t1;
|
|
create table t1_parent (pk1 char(255) primary key) charset=latin1, engine = ndb;
|
|
create table t1 (
|
|
c50 char(255) column_format fixed, c49 char(255) column_format fixed, c48 char(255) column_format fixed, c47 char(255) column_format fixed, c46 char(255) column_format fixed,
|
|
c45 char(255) column_format fixed, c44 char(255) column_format fixed, c43 char(255) column_format fixed, c42 char(255) column_format fixed, c41 char(255) column_format fixed,
|
|
c40 char(255) column_format fixed, c39 char(255) column_format fixed, c38 char(255) column_format fixed, c37 char(255) column_format fixed, c36 char(255) column_format fixed,
|
|
c35 char(255) column_format fixed, c34 char(255) column_format fixed, c33 char(255) column_format fixed, c32 char(255) column_format fixed, c31 char(255) column_format fixed,
|
|
c30 char(255) column_format fixed, c29 char(255) column_format fixed, c28 char(255) column_format fixed, c27 char(255) column_format fixed, c26 char(255) column_format fixed,
|
|
c25 char(255) column_format fixed, c24 char(255) column_format fixed, c23 char(255) column_format fixed, c22 char(255) column_format fixed, c21 char(255) column_format fixed,
|
|
c20 char(255) column_format fixed, c19 char(255) column_format fixed, c18 char(255) column_format fixed, c17 char(255) column_format fixed, c16 char(255) column_format fixed,
|
|
c15 char(255) column_format fixed, c14 char(255) column_format fixed, c13 char(255) column_format fixed, c12 char(255) column_format fixed, c11 char(255) column_format fixed,
|
|
c10 char(255) column_format fixed, c9 char(255) column_format fixed, c8 char(255) column_format fixed, c7 char(255) column_format fixed, c6 char(255) column_format fixed,
|
|
c5 char(255) column_format fixed, c4 char(255) column_format fixed, c3 char(255) column_format fixed, c2 char(255) column_format fixed, c1 char(255) column_format fixed,
|
|
primary key using hash(c1)
|
|
,foreign key(c1) references t1_parent(pk1)
|
|
) charset=latin1, engine=ndb;
|
|
ERROR HY000: Can't create table 't1'
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1296 Got error 851 'Fixed-size column offset exceeded max.Use VARCHAR or COLUMN_FORMAT DYNAMIC for memory-stored columns' from NDB
|
|
Warning 1296 Failed to commit NDB schema transaction
|
|
Error 1005 Can't create table 't1'
|
|
create table t1(a int primary key) engine = ndb;
|
|
drop table t1, t1_parent;
|
|
# bug#17591531 - ERROR ON DROP INDEX WHEN REFERENCING FOREIGN KEYS
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
unique key t1x1 (b) using hash,
|
|
primary key using hash (a)
|
|
) engine=ndb;
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
key (c),
|
|
key t2x1 (b),
|
|
primary key using hash (a),
|
|
constraint t2fk1 foreign key (b) references t1 (b)
|
|
) engine=ndb;
|
|
# try to drop indexes
|
|
alter table t1 drop index t1x1;
|
|
ERROR HY000: Cannot drop index 't1x1': needed in a foreign key constraint
|
|
alter table t2 drop index t2x1;
|
|
ERROR HY000: Cannot drop index 't2x1': needed in a foreign key constraint
|
|
insert into t1 values (1,10),(2,20),(3,30);
|
|
insert into t2 values (1,10,100),(2,20,200),(3,30,300);
|
|
insert into t2 values (4,40,400);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
delete from t1 where a=1;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
drop table t2, t1;
|
|
# bug#17232212 - NDB DROP NON-EXISTENT FK SUCCEEDS
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb;
|
|
# algorithm=inplace
|
|
create table t2 (
|
|
a int primary key,
|
|
b int,
|
|
c int,
|
|
key xb (b),
|
|
key xc (c),
|
|
constraint fkb foreign key (b) references t1 (a),
|
|
constraint fkc foreign key (c) references t1 (a)
|
|
) engine=ndb;
|
|
alter table t2
|
|
drop foreign key nosuchfk,
|
|
algorithm=inplace;
|
|
ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `xb` (`b`),
|
|
KEY `xc` (`c`),
|
|
CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t2
|
|
drop foreign key fkb,
|
|
drop foreign key nosuchfk,
|
|
drop foreign key fkc,
|
|
algorithm=inplace;
|
|
ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `xb` (`b`),
|
|
KEY `xc` (`c`),
|
|
CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t2;
|
|
# algorithm=copy
|
|
create table t2 (
|
|
a int primary key,
|
|
b int,
|
|
c int,
|
|
key xb (b),
|
|
key xc (c),
|
|
constraint fkb foreign key (b) references t1 (a),
|
|
constraint fkc foreign key (c) references t1 (a)
|
|
) engine=ndb;
|
|
alter table t2
|
|
drop foreign key nosuchfk,
|
|
algorithm=copy;
|
|
ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `xb` (`b`),
|
|
KEY `xc` (`c`),
|
|
CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t2
|
|
drop foreign key fkb,
|
|
drop foreign key nosuchfk,
|
|
drop foreign key fkc,
|
|
algorithm=copy;
|
|
ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `xb` (`b`),
|
|
KEY `xc` (`c`),
|
|
CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t2, t1;
|
|
# bug#18662582 - NDB_FK_DISABLE.TEST FAILS TO DROP FOREIGN KEY
|
|
create table t1 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
create table t2 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
|
|
create table t3 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
|
|
alter table t2 algorithm=inplace, drop foreign key nosuchfk;
|
|
ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
|
|
alter table t2 algorithm=inplace, drop foreign key fkname2;
|
|
alter table t3 algorithm=inplace, drop foreign key fkname3;
|
|
drop table t1, t2, t3;
|
|
create table t1 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
create table t2 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
|
|
create table t3 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
|
|
alter table t2 algorithm=copy, drop foreign key nosuchfk;
|
|
ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
|
|
alter table t2 algorithm=copy, drop foreign key fkname2;
|
|
alter table t3 algorithm=copy, drop foreign key fkname3;
|
|
drop table t1, t2, t3;
|
|
# bug#18824753 - NDB_RESTORE FAILS WITH FKS WITH SAME NAME
|
|
create table t1 (a int primary key) engine=ndb;
|
|
create table t2 (a int primary key) engine=ndb;
|
|
create table t3 (a int primary key) engine=ndb;
|
|
create table t4 (a int primary key) engine=ndb;
|
|
alter table t2
|
|
add constraint myfk2 foreign key (a) references t1 (a);
|
|
alter table t4
|
|
add constraint myfk4 foreign key (a) references t3 (a);
|
|
alter table t4
|
|
add constraint `11/22/myfk4` foreign key (a) references t3 (a);
|
|
ERROR HY000: Cannot add foreign key constraint
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1296 Got error 21090 'Create foreign key failed in NDB - name contains invalid character (/)' from NDB
|
|
Error 1215 Cannot add foreign key constraint
|
|
# run backup
|
|
drop table t2, t1;
|
|
drop table t4, t3;
|
|
# run restore meta
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
CONSTRAINT `myfk2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
show create table t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`a` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`),
|
|
CONSTRAINT `myfk4` FOREIGN KEY (`a`) REFERENCES `t3` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t2, t1;
|
|
drop table t4, t3;
|
|
# bug18825966 - ADDING FK TO TABLE WITH DATA GIVES INTERNAL ERROR 208
|
|
create table t1 (
|
|
c0 int,
|
|
c1 int,
|
|
primary key using hash (c1)
|
|
) engine=ndb;
|
|
create table t2 (
|
|
c0 int,
|
|
primary key using hash (c0)
|
|
) engine=ndb;
|
|
insert into t1 (c1) values (1);
|
|
insert into t2 (c0) values (1);
|
|
alter table t2 add constraint foreign key fk (c0) references t1 (c1);
|
|
drop table t2, t1;
|
|
create table t1 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int,
|
|
primary key (c2,c5)
|
|
) engine=ndb;
|
|
create table t2 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int,
|
|
primary key (c1,c4)
|
|
) engine=ndb;
|
|
insert into t1 (c2,c5) values (1,10),(2,20),(3,30);
|
|
insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
|
|
alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
|
|
insert into t2 (c1,c4) values (4,40);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`c1`,`c4`) REFERENCES `t1` (`c2`,`c5`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
drop table t2, t1;
|
|
create table t1 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int not null,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int not null,
|
|
primary key (c0),
|
|
unique key using hash (c2,c5)
|
|
) engine=ndb;
|
|
create table t2 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int,
|
|
primary key (c1,c4)
|
|
) engine=ndb;
|
|
insert into t1 (c0,c2,c5) values (1,1,10),(2,2,20),(3,3,30);
|
|
insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
|
|
alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
|
|
insert into t2 (c1,c4) values (4,40);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`c1`,`c4`) REFERENCES `t1` (`c2`,`c5`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
drop table t2, t1;
|
|
#
|
|
# BUG#21308928 : CANNOT ADD UNIQUE CONSTRAINT ON SET OF FIELDS WITH EXISTING FOREIGN KEY
|
|
#
|
|
#Create the tables
|
|
create table t1 (
|
|
a int not null primary key
|
|
) engine ndb;
|
|
create table t2 (
|
|
a int primary key,
|
|
b int not null,
|
|
c int,
|
|
constraint fk1 foreign key (a) references t1(a),
|
|
constraint fk2 foreign key (b) references t1(a),
|
|
constraint fk3 foreign key (c) references t1(a)
|
|
) engine ndb;
|
|
#Add a unique constraint to the foreign key column
|
|
alter table t2 add constraint uniquet2a unique (a);
|
|
alter table t2 add constraint uniquet2b unique (b);
|
|
alter table t2 add constraint uniquet2c unique (c);
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `uniquet2a` (`a`),
|
|
UNIQUE KEY `uniquet2b` (`b`),
|
|
UNIQUE KEY `uniquet2c` (`c`),
|
|
CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
#Drop tables
|
|
drop table t2, t1;
|
|
#
|
|
# Bug#27930382 : FOREIGN KEY CONSTRAINT VIOLATIONS INCORRECTLY REPORTED
|
|
|
|
create table t1 (
|
|
kol1 int primary key,
|
|
kol2 int not null,
|
|
kol3 int not null,
|
|
kol4 int not null,
|
|
kol5 int not null,
|
|
unique key uq1(kol2),
|
|
foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
|
|
) engine = ndb;
|
|
insert into t1 values (1,1,1,1,1);
|
|
delete from t1 where kol1=1;
|
|
drop table t1;
|
|
create table t1 (
|
|
kol1 int primary key,
|
|
kol2 int not null,
|
|
kol3 int not null,
|
|
kol4 int not null,
|
|
kol5 int not null,
|
|
foreign key fk1(kol3) references t1(kol1) on update restrict on delete restrict
|
|
) engine = ndb;
|
|
insert into t1 values (1,1,1,1,1);
|
|
delete from t1 where kol1=1;
|
|
drop table t1;
|
|
create table t1 (
|
|
kol1 int primary key,
|
|
kol2 int not null,
|
|
kol3 int not null,
|
|
kol4 int not null,
|
|
kol5 int not null,
|
|
unique key uq1(kol2),
|
|
unique key uq2(kol3),
|
|
foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
|
|
) engine = ndb;
|
|
insert into t1 values (1,1,1,1,1);
|
|
delete from t1 where kol1=1;
|
|
insert into t1 values (2,1,1,1,1);
|
|
delete from t1 where kol1=2;
|
|
insert into t1 values (3,1,1,1,1);
|
|
delete from t1 where kol1=3;
|
|
insert into t1 values (4,1,1,1,1);
|
|
delete from t1 where kol1=4;
|
|
insert into t1 values (1,0,0,0,0);
|
|
insert into t1 values (2,1,1,1,1);
|
|
insert into t1 values (3,2,2,2,2);
|
|
insert into t1 values (4,3,3,3,3);
|
|
delete from t1;
|
|
drop table t1;
|