#
# Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
# DICT_CREATE_FOREIGN_CONSTR
#
create table t1 (f1 int primary key) engine=InnoDB;
create table t2 (f1 int primary key,
constraint c1 foreign key (f1) references t1(f1),
constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
ERROR HY000: Duplicate foreign key constraint name 'c1'
create table t2 (f1 int primary key,
constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
ERROR HY000: Duplicate foreign key constraint name 'c1'
set foreign_key_checks = 0;
alter table t2 add constraint c1 foreign key (f1) references t1(f1);
ERROR HY000: Duplicate foreign key constraint name 'c1'
drop table t2, t1;
#
# Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
# NULL/NOT NULL MISMATCH
#
set foreign_key_checks = 1;
show variables like 'foreign_key_checks';
Variable_name	Value
foreign_key_checks	ON
CREATE TABLE t1
(a INT NOT NULL,
b INT NOT NULL,
INDEX idx(a)) ENGINE=InnoDB;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int NOT NULL,
  `b` int NOT NULL,
  KEY `idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `ind` (`b`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO t1 VALUES (1, 80);
INSERT INTO t1 VALUES (2, 81);
INSERT INTO t1 VALUES (3, 82);
INSERT INTO t1 VALUES (4, 83);
INSERT INTO t1 VALUES (5, 84);
INSERT INTO t2 VALUES (51, 1);
INSERT INTO t2 VALUES (52, 2);
INSERT INTO t2 VALUES (53, 3);
INSERT INTO t2 VALUES (54, 4);
INSERT INTO t2 VALUES (55, 5);
SELECT a, b FROM t1 ORDER BY a;
a	b
1	80
2	81
3	82
4	83
5	84
SELECT a, b FROM t2 ORDER BY a;
a	b
51	1
52	2
53	3
54	4
55	5
INSERT INTO t2 VALUES (56, 6);
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE)
NewDD TODO: Runtime Bug#25722221 - RENAME COLUMN DID NOT UPDATE MYSQL.FOREIGN_KEY_COLUMN_USAGE FOR FK CONSTRAINT
# Operations on child table
TODO: Waiting for Bug#24666169
DROP TABLE t2, t1;
#
# Bug#20752436: INNODB: FAILING ASSERTION: 0 IN FILE HANDLER0ALTER.CC
# LINE 6647
#
# Verify that index types that cannot be used as foreign keys are
# ignored when creating foreign keys.
set @@foreign_key_checks=0;
CREATE TABLE t1(a CHAR(100), b GEOMETRY NOT NULL SRID 0) ENGINE InnoDB;
# Creating a foreign key on a GEOMETRY column is not supported
ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fi_b' in the foreign table 't1'
# Adds FULLTEXT and SPATAL indices which cannot be used as foreign keys
ALTER TABLE t1 ADD FULLTEXT INDEX(a), ADD SPATIAL INDEX(b);
Warnings:
Warning	124	InnoDB rebuilding table to add column FTS_DOC_ID
# Adds a foreign key on column with FULLTEXT index.
# The FULLTEXT index cannot be used and the generated key must be kept
ALTER TABLE t1 ADD CONSTRAINT fi_a FOREIGN KEY(a) REFERENCES ti2(a);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
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	1	b	1	b	A	0	32	NULL		SPATIAL			YES	NULL
t1	1	fi_a	1	a	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	a	1	a	NULL	0	NULL	NULL	YES	FULLTEXT			YES	NULL
# Attempt to add a foreign key on column with SPATIAL index.
# The SPATIAL index cannot be used so this becomes an attempt at
# creating a foreign key on a GEOMETRY column which is not supported
ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
ERROR HY000: Cannot drop index 'fi_a': needed in a foreign key constraint
DROP TABLE t1;
set @@foreign_key_checks= 1;
# bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
# base bug#24818604 [GR]
#
CREATE TABLE t1 (c1 INT PRIMARY KEY);
CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1));
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
UNIQUE_CONSTRAINT_NAME
PRIMARY
# restart
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
UNIQUE_CONSTRAINT_NAME
PRIMARY
SELECT * FROM t1;
c1
1
SELECT unique_constraint_name FROM information_schema.referential_constraints
WHERE table_name = 't2';
UNIQUE_CONSTRAINT_NAME
PRIMARY
DROP TABLE t2;
DROP TABLE t1;
#
# BUG#25976199 GROUP_REPLICATION.GR_FOREIGN_CHECKS_DISABLED TEST FAILING
# ON PB2
#
SET foreign_key_checks=0;
CREATE TABLE child_table (c1 INT PRIMARY KEY,
FOREIGN KEY (c1) REFERENCES parent_table(c1));
CREATE TABLE parent_table (c1 INT PRIMARY KEY);
SET foreign_key_checks=1;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN WHERE
ID='test/child_table_ibfk_1';
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/child_table_ibfk_1	test/child_table	test/parent_table	1	48
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE
ID='test/child_table_ibfk_1';
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/child_table_ibfk_1	c1	c1	1
DROP TABLE parent_table;
ERROR HY000: Cannot drop table 'parent_table' referenced by a foreign key constraint 'child_table_ibfk_1' on table 'child_table'.
DROP TABLE child_table;
DROP TABLE parent_table;
#
# Bug#26764604 - INNODB: FAILING ASSERTION: COL_NR < TABLE->N_DEF
#
SELECT @@foreign_key_checks;
@@foreign_key_checks
1
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
unique1 int(11) DEFAULT NULL,
unique2 int(11) DEFAULT NULL,
two int(11) DEFAULT NULL,
four int(11) DEFAULT NULL,
ten int(11) DEFAULT NULL,
twenty int(11) DEFAULT NULL,
forty int(11) DEFAULT NULL,
sixty int(11) DEFAULT NULL,
eighty int(11) DEFAULT NULL,
tenPercent int(11) DEFAULT NULL,
twentyPercent int(11) DEFAULT NULL,
fiftyPercent int(11) DEFAULT NULL,
unique1gc int(11) GENERATED ALWAYS AS ((unique1 + unique2)) VIRTUAL,
unique2gc int(11) GENERATED ALWAYS AS ((unique1 * unique2)) VIRTUAL,
twogc int(11) GENERATED ALWAYS AS (((two * two) + 1)) VIRTUAL,
fourgc int(11) GENERATED ALWAYS AS (((four + four) - 1)) VIRTUAL,
tengc int(11) GENERATED ALWAYS AS ((twogc + ten)) VIRTUAL,
twentygc int(11) GENERATED ALWAYS AS ((((twenty * twogc) * unique1) % 20)) VIRTUAL,
fortygc int(11) GENERATED ALWAYS AS (forty) VIRTUAL,
sixtygc int(11) GENERATED ALWAYS AS (sixty) VIRTUAL,
eightygc int(11) GENERATED ALWAYS AS ((((eighty - twentygc) - ten) - two)) VIRTUAL,
PRIMARY KEY (pk),
KEY allkey(unique1,unique2,two,four,ten,twenty,forty,sixty,eighty,tenPercent,twentyPercent,fiftyPercent),
KEY kugc1 (unique1gc),
KEY kugc2 (unique2gc),
KEY kumixgc1 (unique1gc,unique2gc),
KEY kgc1 (twogc,fourgc),
KEY kgc2 (fourgc,tengc),
KEY kgc3 (tengc,twentygc),
KEY kgc4 (twentygc,fortygc),
KEY kgc5 (fortygc,sixtygc),
KEY kgc6 (sixtygc,eightygc),
KEY allkeygc(unique1gc,unique2gc,twogc,fourgc,tengc,twentygc,fortygc,sixtygc,eightygc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t2_fk (
pk int(11) NOT NULL,
sixty int(11) DEFAULT NULL,
PRIMARY KEY (pk),
CONSTRAINT fk6 FOREIGN KEY (sixty) REFERENCES t1(sixty)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk6' in the referenced table 't1'
DROP TABLE t1;
#
# Bug#27014308 - UPGRADING TO 8.0.3 FAILS IF FOREIGN KEYS OF EXACTLY 64 CHARACTERS ARE PRESENT
#
# test with FK identifier of length exactly 64 characters;
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
INT,
INDEX par_ind
(xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
CONSTRAINT
`xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
FOREIGN
KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
DROP TABLE child;
DROP TABLE parent;
# test with FK identifier of length exactly 65 characters,
# child table creation should fail
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
INT,
INDEX par_ind
(xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
CONSTRAINT
`xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
FOREIGN
KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
ERROR 42000: Identifier name 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' is too long
DROP TABLE parent;
# Test delete/update rules combination.
CREATE TABLE t1
(a INT NOT NULL,
b INT NOT NULL,
INDEX idx(a)) ENGINE=InnoDB;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a))
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	48
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	33
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	34
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	48
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	20
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE SET NULL)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	24
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE NO ACTION)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	48
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	5
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE SET NULL)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	9
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE NO ACTION)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	33
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE RESTRICT)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	1
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE CASCADE)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	6
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE SET NULL)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	10
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE NO ACTION)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	34
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE RESTRICT)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	2
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE CASCADE)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	20
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE SET NULL)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	24
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE NO ACTION)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	48
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE RESTRICT)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	16
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE CASCADE)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	4
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE SET NULL)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	8
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE NO ACTION)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	32
DROP TABLE t2;
CREATE TABLE t2
(a INT KEY,
b INT,
INDEX ind(b),
FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE RESTRICT)
ENGINE=InnoDB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/t2_ibfk_1	test/t2	test/t1	1	0
DROP TABLE t2;
DROP TABLE t1;
#
# Bug#28581468 - RENAMING PARENT COLUMN IN A FOREIGN KEY FAILS WITH STRANGE ERROR.
#
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
ALTER TABLE t1 CHANGE a id INT;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
ALTER TABLE t1 CHANGE a id INT NOT NULL;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
CHECK TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	check	status	OK
DROP TABLE t1, t2;
#
# Bug #27020089 "I_S.INNODB_SYS_FOREIGN LOST FOREIGN KEY INFORMATION."
#
CREATE TABLE t1(pk INT PRIMARY KEY);
CREATE TABLE `......................................`
  (fk INT, FOREIGN KEY(fk) REFERENCES t1(pk));
SHOW CREATE TABLE `......................................`;
Table	Create Table
......................................	CREATE TABLE `......................................` (
  `fk` int DEFAULT NULL,
  KEY `fk` (`fk`),
  CONSTRAINT `......................................_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT id, for_name, ref_name FROM information_schema.innodb_foreign WHERE id LIKE 'test%';
id	for_name	ref_name
test/......................................_ibfk_1	test/......................................	test/t1
DROP TABLES `......................................`, t1;
#
# Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
#
#Test-Case 1
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_date` date GENERATED ALWAYS AS
(concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
'-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `IDX_ES1` (`email_id`),
KEY `mautic_generated_sent_date_email_id`
(`generated_sent_date`,`email_id`),
CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
SELECT * FROM `email_stats`;
id	email_id	date_sent	generated_sent_date
1	1	2020-10-22 13:32:41	2020-10-22
DELETE FROM `emails`;
DELETE FROM `email_stats`;
DROP TABLE `email_stats`;
DROP TABLE `emails`;
# Test-Case 2
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_date` date GENERATED ALWAYS AS
(concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
'-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `IDX_ES1` (`email_id`),
KEY `mautic_generated_sent_date_email_id`
(`generated_sent_date`,`email_id`),
CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
UPDATE `emails` SET `id` = 2 where `id` = 1;
SELECT id FROM `email_stats` WHERE `generated_sent_date` IS NULL;
id
SELECT * FROM `email_stats`;
id	email_id	date_sent	generated_sent_date
1	NULL	2020-10-22 13:32:41	2020-10-22
UPDATE `email_stats` SET `email_id`=2
WHERE DATE(`generated_sent_date`) = '2020-10-22';
SELECT * FROM `email_stats`;
id	email_id	date_sent	generated_sent_date
1	2	2020-10-22 13:32:41	2020-10-22
DROP TABLE `email_stats`;
DROP TABLE `emails`;
# test-case 3
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_email` varchar(20) GENERATED ALWAYS AS
(CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_es1` (`email_id`),
KEY `mautic_generated_sent_date_email`
(`generated_sent_email`,`email_id`),
CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
SELECT * FROM `email_stats`;
id	email_id	date_sent	generated_sent_email
1	1	2020-10-22 13:32:41	2020-1
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
date_sent
2020-10-22 13:32:41
DELETE FROM `emails`;
SELECT * FROM `email_stats`;
id	email_id	date_sent	generated_sent_email
1	NULL	2020-10-22 13:32:41	2020- 
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
date_sent
2020-10-22 13:32:41
DROP TABLE `email_stats`;
DROP TABLE `emails`;
# test-case 4
CREATE TABLE `emails` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
CREATE TABLE `email_stats` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`email_id` int unsigned DEFAULT NULL,
`date_sent` datetime NOT NULL,
`generated_sent_email` varchar(20) GENERATED ALWAYS AS
(CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_es1` (`email_id`),
KEY `mautic_generated_sent_date_email`
(`generated_sent_email`,`email_id`),
CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
`emails` (`id`) ON UPDATE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC;
INSERT INTO `emails` VALUES (1);
INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES
(1,1,'2020-10-22 13:32:41');
SELECT * FROM `email_stats`;
id	email_id	date_sent	generated_sent_email
1	1	2020-10-22 13:32:41	2020-1
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
date_sent
2020-10-22 13:32:41
UPDATE `emails` SET `id` = 2 WHERE `id` = 1;
SELECT * FROM `email_stats`;
id	email_id	date_sent	generated_sent_email
1	NULL	2020-10-22 13:32:41	2020- 
SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
date_sent
2020-10-22 13:32:41
DROP TABLE `email_stats`;
DROP TABLE `emails`;