DROP TABLE IF EXISTS t1;
create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) )
engine=ndb;
insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three');
create index c on t1(c);
show indexes from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	0	PRIMARY	1	a	A	3	NULL	NULL		BTREE			YES	NULL
t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES	NULL
t1	1	c	1	c	A	NULL	NULL	NULL	YES	BTREE			YES	NULL
select * from t1 where c = 'two';
a	b	c
2	two	two
alter table t1 drop index c;
show indexes from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	0	PRIMARY	1	a	A	3	NULL	NULL		BTREE			YES	NULL
t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES	NULL
select * from t1 where c = 'two';
a	b	c
2	two	two
drop table t1;
create table t3 (a int primary key) engine=ndbcluster;
insert into t3 values (1);
alter table t3 rename t4;
select * from t3;
ERROR 42S02: Table 'test.t3' doesn't exist
select * from t4;
a
1
drop table t4;
show tables;
Tables_in_test
drop table if exists t6;
create table t6 (a int primary key ,b int) engine=ndbcluster;
insert into t6 values(1,12), (2,12);
alter table t6 add unique key ui_t(b);
ERROR 23000: Can't write, because of unique constraint, to table 't6'
delete from t6;
insert into t6 values(1,12), (2,12);
alter table t6 add unique key ui_t(b);
ERROR 23000: Can't write, because of unique constraint, to table 't6'
delete from t6;
drop table t6;
#
# Bug#22173891 : CIRCULAR FOREIGN KEYS + INDEX = FAILURE
#
#create table t1
create table t1(
a int primary key,
b int unique key
)engine = ndb;
#create table t2 with fks referring columns from t1
create table t2(
a int,
b int,
c int unique key,
constraint fk1 foreign key (a) references t1(a),
constraint fk2 foreign key (b) references t1(b)
)engine = ndb;
#t2 now has additional keys on col `a` and `b` to support fks.
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  UNIQUE KEY `c` (`c`),
  KEY `fk1` (`a`),
  KEY `fk2` (`b`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
#create an index on t2(a) using 'create index'
create index id1 on t2(a);
#verify that the implicit key on col `a` is gone now and fk is intact.
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  UNIQUE KEY `c` (`c`),
  KEY `fk2` (`b`),
  KEY `id1` (`a`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
insert into t1 values (1,10), (2,20), (3,30), (4,40);
insert into t2 values (1,10,100), (2,20,200);
insert into t2 values (5,50,500);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_name` FOREIGN KEY (`col_name`) REFERENCES `t1` (`col_name`) ON DELETE NO ACTION ON UPDATE NO ACTION)
#create a table t3 with an fk refering to t2(c)
create table t3(
a int,
constraint fk3 foreign key (a) references t2(c)
)engine = ndb;
#create an index on col `b` on t2
create unique index id2 on t2(b);
#verify that the implicit key on col `b` is also gone now.
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  UNIQUE KEY `c` (`c`),
  UNIQUE KEY `id2` (`b`),
  KEY `id1` (`a`),
  CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
#verify that the fks are intact in t2 and t3
insert into t2 values (3,30,300), (4,40,400);
insert into t2 values (5,50,500);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_name` FOREIGN KEY (`col_name`) REFERENCES `t1` (`col_name`) ON DELETE NO ACTION ON UPDATE NO ACTION)
insert into t3 values (100), (200);
insert into t3 values (500);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `fk3` FOREIGN KEY (`a`) REFERENCES `t2` (`c`) ON DELETE NO ACTION ON UPDATE NO ACTION)
#cleanup
drop table t3, t2, t1;