80 lines
2.9 KiB
Plaintext
80 lines
2.9 KiB
Plaintext
select @@global.lower_case_table_names;
|
|
@@global.lower_case_table_names
|
|
1
|
|
# test FK name is stored lower case
|
|
create table T1 (
|
|
a int primary key
|
|
) engine=ndb;
|
|
insert into t1 values (1);
|
|
create table T2 (
|
|
a int primary key,
|
|
b int,
|
|
constraint myFK1 foreign key (a) references t1 (a),
|
|
key XB (b)
|
|
) engine=ndb;
|
|
alter table T2
|
|
add constraint MYfk2 foreign key (b) references test.t1 (a);
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `XB` (`b`),
|
|
CONSTRAINT `myfk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `myfk2` 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 values (2,1);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `myfk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
insert into t2 values (1,2);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `myfk2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
|
|
ndb_show_tables completed.....
|
|
|
|
select count(*) from ndb_show_tables_results
|
|
where type = "'UserTable'" and name in ( "'t1'", "'t2'");
|
|
count(*)
|
|
2
|
|
select count(*) from ndb_show_tables_results
|
|
where type = "'ForeignKey'" and name like "'%myfk%'";
|
|
count(*)
|
|
2
|
|
alter table T2
|
|
drop foreign key MYfk1;
|
|
alter table t2
|
|
drop foreign key myFK2,
|
|
algorithm=copy;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `XB` (`b`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
# test upper case parent database and table
|
|
drop table T2;
|
|
create table T2 (
|
|
a int primary key,
|
|
b int,
|
|
constraint myFK1 foreign key (a) references T1 (a),
|
|
key XB (b)
|
|
) engine=ndb;
|
|
alter table T2
|
|
add constraint MYfk2 foreign key (b) references TEST.T1 (a);
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `XB` (`b`),
|
|
CONSTRAINT `myfk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
|
CONSTRAINT `myfk2` 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 values (2,1);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `myfk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
insert into t2 values (1,2);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `myfk2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
|
|
drop table t2,t1;
|