MDEV-20865 check referenced fields existence and typebb-10.6-midenok-MDEV-16417

Introduced fk_prepare_create_table() which is called at
mysql_prepare_create_table() that checks fields existence and fixes
names case according to fields in ref-table or table definition
(innodb-alter).

Remove deprecated branch in fk_handle_alter().

18 files changed, 281 insertions, 83 deletions

diff --git a/mysql-test/main/foreign_key.result b/mysql-test/main/foreign_key.result
index 4f1cbd960b0..f46500d40e2 100644
--- a/mysql-test/main/foreign_key.result
+++ b/mysql-test/main/foreign_key.result
@@ -649,7 +649,7 @@ create or replace table t2 (id int, a int, foreign key fk (id) references t1 (id
 alter table t1 drop id;
 ERROR HY000: Cannot drop column 'id': needed in a foreign key constraint 'fk' of table `test`.`t2`
 alter table t2 drop id;
-ERROR HY000: Cannot drop index 'fk': needed in a foreign key constraint
+ERROR 42000: Incorrect foreign key definition for 'id'
 alter table t2 drop foreign key fk;
 drop tables t1, t2;
 # Check self-references
@@ -793,3 +793,41 @@ delete from t2;
 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t1_2` FOREIGN KEY (`id3`) REFERENCES `t2` (`id4`))
 drop table t2;
 set default_storage_engine= default;
+# Prohibit wrong references and fix field name case
+create table t1 (A int unique key, x timestamp references t1(a));
+ERROR 42000: Incorrect foreign key definition for 'x'
+create table t1 (A int unique key, x int references t1(b));
+ERROR 42000: Incorrect foreign key definition for 'b'
+create table t1 (A int unique key, x int references t1(a));
+alter table t1 add foreign key(x) references t1(b);
+ERROR 42000: Incorrect foreign key definition for 'b'
+alter table t1 add foreign key(x) references t1(a);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `A` int(11) DEFAULT NULL,
+  `x` int(11) DEFAULT NULL,
+  UNIQUE KEY `A` (`A`),
+  KEY `fk_t1_2` (`x`),
+  CONSTRAINT `fk_t1` FOREIGN KEY (`x`) REFERENCES `t1` (`A`),
+  CONSTRAINT `fk_t1_2` FOREIGN KEY (`x`) REFERENCES `t1` (`A`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create table t2 (AA int, x int references t1(ax));
+ERROR 42000: Incorrect foreign key definition for 'ax'
+create table t2 (AA int, x timestamp references t1(a));
+ERROR 42000: Incorrect foreign key definition for 'x'
+create table t2 (AA int, x int references t1(a));
+alter table t2 add foreign key(aa) references t1(b);
+ERROR 42000: Incorrect foreign key definition for 'b'
+alter table t2 add foreign key(aa) references t1(a);
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `AA` int(11) DEFAULT NULL,
+  `x` int(11) DEFAULT NULL,
+  KEY `fk_t2` (`x`),
+  KEY `fk_t2_2` (`AA`),
+  CONSTRAINT `fk_t2` FOREIGN KEY (`x`) REFERENCES `t1` (`A`),
+  CONSTRAINT `fk_t2_2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop tables t2, t1;
diff --git a/mysql-test/main/foreign_key.test b/mysql-test/main/foreign_key.test
index ce3a870db86..0c68b737649 100644
--- a/mysql-test/main/foreign_key.test
+++ b/mysql-test/main/foreign_key.test
@@ -399,7 +399,7 @@ create or replace table t1 (id int primary key, a int);
 create or replace table t2 (id int, a int, foreign key fk (id) references t1 (id));
 --error ER_FK_COLUMN_CANNOT_DROP_CHILD
 alter table t1 drop id;
---error ER_DROP_INDEX_FK
+--error ER_WRONG_FK_DEF
 alter table t2 drop id;
 alter table t2 drop foreign key fk;
 drop tables t1, t2;
@@ -453,3 +453,28 @@ delete from t2;
 drop table t2;
 
 set default_storage_engine= default;
+
+--echo # Prohibit wrong references and fix field name case
+--error ER_WRONG_FK_DEF
+create table t1 (A int unique key, x timestamp references t1(a));
+--error ER_WRONG_FK_DEF
+create table t1 (A int unique key, x int references t1(b));
+create table t1 (A int unique key, x int references t1(a));
+--error ER_WRONG_FK_DEF
+alter table t1 add foreign key(x) references t1(b);
+alter table t1 add foreign key(x) references t1(a);
+
+show create table t1;
+--error ER_WRONG_FK_DEF
+create table t2 (AA int, x int references t1(ax));
+--error ER_WRONG_FK_DEF
+create table t2 (AA int, x timestamp references t1(a));
+
+create table t2 (AA int, x int references t1(a));
+
+--error ER_WRONG_FK_DEF
+alter table t2 add foreign key(aa) references t1(b);
+alter table t2 add foreign key(aa) references t1(a);
+
+show create table t2;
+drop tables t2, t1;
diff --git a/mysql-test/suite/innodb/r/alter_table.result b/mysql-test/suite/innodb/r/alter_table.result
index 21588ee303a..d18b96ee7bc 100644
--- a/mysql-test/suite/innodb/r/alter_table.result
+++ b/mysql-test/suite/innodb/r/alter_table.result
@@ -46,11 +46,11 @@ CREATE TABLE tx (pk INT PRIMARY KEY) ENGINE=InnoDB;
 CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk), KEY (a), FOREIGN KEY (a) REFERENCES tx (pk)) ENGINE=InnoDB;
 SET FOREIGN_KEY_CHECKS=OFF;
 ALTER TABLE t1 DROP a;
-ERROR HY000: Cannot drop column 'a': needed in a foreign key constraint 'test/fk_t1'
+ERROR 42000: Incorrect foreign key definition for 'a'
 SET FOREIGN_KEY_CHECKS=ON;
 ALTER TABLE t1 ADD b INT;
 ALTER TABLE t1 DROP a;
-ERROR HY000: Cannot drop index 'a': needed in a foreign key constraint
+ERROR 42000: Incorrect foreign key definition for 'a'
 ALTER TABLE t1 ADD c INT;
 DROP TABLE t1, tx;
 #
diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index e41cb98bc58..124fa41c4cc 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -235,21 +235,17 @@ DROP TABLE t2, t1;
 CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB;
 SET SESSION FOREIGN_KEY_CHECKS = OFF;
 ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
-SET SESSION FOREIGN_KEY_CHECKS = ON;
 ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
-Warnings:
-Warning	1088	failed to load FOREIGN KEY constraints
 ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
+SET SESSION FOREIGN_KEY_CHECKS = ON;
 DROP TABLE t1;
 CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY)
 ENGINE=InnoDB;
 SET SESSION FOREIGN_KEY_CHECKS = OFF;
 ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
-SET SESSION FOREIGN_KEY_CHECKS = ON;
 ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
-Warnings:
-Warning	1088	failed to load FOREIGN KEY constraints
 ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
+SET SESSION FOREIGN_KEY_CHECKS = ON;
 DROP TABLE t1;
 #
 # MDEV-18630 Conditional jump or move depends on uninitialised value
diff --git a/mysql-test/suite/innodb/r/foreign_key_legacy.result b/mysql-test/suite/innodb/r/foreign_key_legacy.result
index 144df971a50..f38cf3e4948 100644
--- a/mysql-test/suite/innodb/r/foreign_key_legacy.result
+++ b/mysql-test/suite/innodb/r/foreign_key_legacy.result
@@ -438,7 +438,7 @@ ERROR HY000: Can't create table `test`.`t1` (errno: 197 "Table requires foreign
 alter table t1 drop column x;
 ERROR HY000: Cannot drop index 'x': needed in a foreign key constraint
 alter table t2 drop column y;
-ERROR HY000: Cannot drop index 'fk_t2': needed in a foreign key constraint
+ERROR 42000: Incorrect foreign key definition for 'y'
 set global innodb_background_drop_list_empty= 1;
 select * from information_schema.innodb_sys_foreign;
 ERROR 42S02: Unknown table 'innodb_sys_foreign'
diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result
index 5a6cb613ff9..f661a76216b 100644
--- a/mysql-test/suite/innodb/r/innodb-alter.result
+++ b/mysql-test/suite/innodb/r/innodb-alter.result
@@ -659,7 +659,7 @@ Table	Create Table
 t2	CREATE TABLE `t2` (
   `C2` int(11) DEFAULT NULL,
   KEY `fk_t2` (`C2`),
-  CONSTRAINT `fk_t2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`)
+  CONSTRAINT `fk_t2` FOREIGN KEY (`C2`) REFERENCES `t1` (`C1`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
 ALTER TABLE t2 CHANGE COLUMN C2 c6 INT;
@@ -758,27 +758,27 @@ t2	CREATE TABLE `t2` (
   KEY `fk_t2_2` (`BB`),
   KEY `fk_t2_3` (`CC`),
   KEY `fk_t2_4` (`DD`),
-  CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`),
-  CONSTRAINT `fk_t2_2` FOREIGN KEY (`bb`) REFERENCES `t1` (`b`),
-  CONSTRAINT `fk_t2_3` FOREIGN KEY (`cc`) REFERENCES `t1` (`c`),
-  CONSTRAINT `fk_t2_4` FOREIGN KEY (`dd`) REFERENCES `t1` (`d`)
+  CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`),
+  CONSTRAINT `fk_t2_2` FOREIGN KEY (`BB`) REFERENCES `t1` (`B`),
+  CONSTRAINT `fk_t2_3` FOREIGN KEY (`CC`) REFERENCES `t1` (`C`),
+  CONSTRAINT `fk_t2_4` FOREIGN KEY (`DD`) REFERENCES `t1` (`D`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 DELETE FROM t1 WHERE a=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DELETE FROM t1 WHERE A=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DELETE FROM t1 WHERE b=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DELETE FROM t1 WHERE B=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DELETE FROM t1 WHERE c=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DELETE FROM t1 WHERE C=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DELETE FROM t1 WHERE d=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DELETE FROM t1 WHERE D=1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`aa`) REFERENCES `t1` (`a`))
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`AA`) REFERENCES `t1` (`A`))
 DROP TABLE t2, t1;
 # virtual columns case too
 CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB;
diff --git a/mysql-test/suite/innodb/r/innodb-fk-warnings.result b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
index cf1c7822a52..4e08b2b61f6 100644
--- a/mysql-test/suite/innodb/r/innodb-fk-warnings.result
+++ b/mysql-test/suite/innodb/r/innodb-fk-warnings.result
@@ -55,6 +55,7 @@ create temporary table t2(a int, foreign key(a) references t1(a)) engine=innodb;
 ERROR 42S02: Table 'test.t1' doesn't exist
 show warnings;
 Level	Code	Message
+Error	1239	Incorrect foreign key definition for 't1'
 alter table t1 add foreign key(b) references t1(a);
 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
 show warnings;
@@ -93,10 +94,8 @@ Warning	1215	Cannot add foreign key constraint for `t2`
 drop table t1;
 create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb;
 create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb;
-ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+ERROR 42000: Incorrect foreign key definition for 'a'
 show warnings;
 Level	Code	Message
-Warning	150	Create  table `test`.`t2` with foreign key `fk_t2` constraint failed. Field type or character set for column 'a' does not mach referenced column 'f1'.
-Error	1005	Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
-Warning	1215	Cannot add foreign key constraint for `t2`
+Error	1239	Incorrect foreign key definition for 'a'
 drop table t1;
diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result
index 0ce40383ce5..3a4263c14c4 100644
--- a/mysql-test/suite/innodb/r/innodb-fk.result
+++ b/mysql-test/suite/innodb/r/innodb-fk.result
@@ -52,6 +52,7 @@ CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE
 ERROR 42S02: Table 'test.t3' doesn't exist
 show warnings;
 Level	Code	Message
+Error	1239	Incorrect foreign key definition for 't3'
 CREATE TABLE t2 (
 id int(11) NOT NULL AUTO_INCREMENT,
 f2 int(11) NOT NULL,
@@ -200,5 +201,5 @@ b int,
 c0123456789012345678 int,
 FOREIGN KEY (a012345678901234567,c0123456789012345678,b) REFERENCES tx (x1,x2,x3)
 ) ENGINE=InnoDB;
-ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
+ERROR 42000: Incorrect foreign key definition for 'x1'
 drop table tx;
diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result
index 484323a980e..2c84a79df3b 100644
--- a/mysql-test/suite/innodb/r/innodb-index.result
+++ b/mysql-test/suite/innodb/r/innodb-index.result
@@ -463,9 +463,9 @@ ERROR HY000: Column 'b' cannot be NOT NULL: needed in a foreign key constraint '
 set @@sql_mode = @old_sql_mode;
 SET FOREIGN_KEY_CHECKS=0;
 alter table t2 DROP COLUMN b, ALGORITHM=COPY;
-ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 'b'
+ERROR 42000: Incorrect foreign key definition for 'b'
 alter table t2 DROP COLUMN b;
-ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 'test/b'
+ERROR 42000: Incorrect foreign key definition for 'b'
 alter table t1 DROP COLUMN b, ALGORITHM=COPY;
 ERROR HY000: Cannot drop column 'b': needed in a foreign key constraint 'b' of table `test`.`t2`
 alter table t1 DROP COLUMN b;
diff --git a/mysql-test/suite/innodb/t/alter_table.test b/mysql-test/suite/innodb/t/alter_table.test
index 26f3a9f013f..fad82224192 100644
--- a/mysql-test/suite/innodb/t/alter_table.test
+++ b/mysql-test/suite/innodb/t/alter_table.test
@@ -50,13 +50,13 @@ CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk), KEY (a), FOREIGN KEY (a) REFER
 
 SET FOREIGN_KEY_CHECKS=OFF;
 
---error ER_FK_COLUMN_CANNOT_DROP
+--error ER_WRONG_FK_DEF
 ALTER TABLE t1 DROP a;
 
 SET FOREIGN_KEY_CHECKS=ON;
 
 ALTER TABLE t1 ADD b INT;
---error ER_DROP_INDEX_FK
+--error ER_WRONG_FK_DEF
 ALTER TABLE t1 DROP a;
 ALTER TABLE t1 ADD c INT;
 DROP TABLE t1, tx;
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 3848fa82fed..2f957cbea93 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -223,18 +223,18 @@ DROP TABLE t2, t1;
 CREATE TABLE t1 (f VARCHAR(256)) ENGINE=InnoDB;
 SET SESSION FOREIGN_KEY_CHECKS = OFF;
 ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
-SET SESSION FOREIGN_KEY_CHECKS = ON;
 ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
 ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
+SET SESSION FOREIGN_KEY_CHECKS = ON;
 DROP TABLE t1;
 
 CREATE TABLE t1 (f VARCHAR(256), FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY)
 ENGINE=InnoDB;
 SET SESSION FOREIGN_KEY_CHECKS = OFF;
 ALTER TABLE t1 ADD FOREIGN KEY (f) REFERENCES non_existing_table (x);
-SET SESSION FOREIGN_KEY_CHECKS = ON;
 ALTER TABLE t1 ADD FULLTEXT INDEX ft1 (f);
 ALTER TABLE t1 ADD FULLTEXT INDEX ft2 (f);
+SET SESSION FOREIGN_KEY_CHECKS = ON;
 DROP TABLE t1;
 
 --echo #
diff --git a/mysql-test/suite/innodb/t/foreign_key_legacy.test b/mysql-test/suite/innodb/t/foreign_key_legacy.test
index 42a2bc3b676..65593601285 100644
--- a/mysql-test/suite/innodb/t/foreign_key_legacy.test
+++ b/mysql-test/suite/innodb/t/foreign_key_legacy.test
@@ -406,7 +406,7 @@ alter table t1 drop column x, algorithm=copy;
 alter table t1 change x x char(10);
 --error ER_DROP_INDEX_FK
 alter table t1 drop column x;
---error ER_DROP_INDEX_FK
+--error ER_WRONG_FK_DEF
 alter table t2 drop column y;
 
 set global innodb_background_drop_list_empty= 1;
diff --git a/mysql-test/suite/innodb/t/innodb-fk-warnings.test b/mysql-test/suite/innodb/t/innodb-fk-warnings.test
index 9f3214eb5bc..f2b5c53e149 100644
--- a/mysql-test/suite/innodb/t/innodb-fk-warnings.test
+++ b/mysql-test/suite/innodb/t/innodb-fk-warnings.test
@@ -98,7 +98,7 @@ drop table t1;
 # Incorrect types
 #
 create table t1 (id int not null primary key, f1 int, f2 int, key(f1)) engine=innodb;
---error ER_CANT_CREATE_TABLE
+--error ER_WRONG_FK_DEF
 create table t2(a char(20), key(a), foreign key(a) references t1(f1)) engine=innodb;
 show warnings;
 drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test
index 6157a429ff4..9f494af6331 100644
--- a/mysql-test/suite/innodb/t/innodb-fk.test
+++ b/mysql-test/suite/innodb/t/innodb-fk.test
@@ -238,7 +238,7 @@ CREATE TABLE t1 (
     FOREIGN KEY ( a012345678901234567890123456789012345678901, b ) REFERENCES tx (ax, bx)
 ) ENGINE=InnoDB;
 
---error ER_CANT_CREATE_TABLE
+--error ER_WRONG_FK_DEF
 CREATE TABLE t1 (
     a012345678901234567 int,
     b int,
diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test
index b0fb0769c43..05a41814ee0 100644
--- a/mysql-test/suite/innodb/t/innodb-index.test
+++ b/mysql-test/suite/innodb/t/innodb-index.test
@@ -178,9 +178,9 @@ alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE;
 set @@sql_mode = @old_sql_mode;
 
 SET FOREIGN_KEY_CHECKS=0;
---error ER_FK_COLUMN_CANNOT_DROP
+--error ER_WRONG_FK_DEF
 alter table t2 DROP COLUMN b, ALGORITHM=COPY;
---error ER_FK_COLUMN_CANNOT_DROP
+--error ER_WRONG_FK_DEF
 alter table t2 DROP COLUMN b;
 --error ER_FK_COLUMN_CANNOT_DROP_CHILD
 alter table t1 DROP COLUMN b, ALGORITHM=COPY;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 0c0d9c230b7..beb2a2f32ff 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -93,6 +93,10 @@ static
 bool fk_handle_drop(THD* thd, TABLE_LIST* table, FK_backup_storage& shares,
                     bool drop_db);
 
+static
+bool fk_prepare_create_table(THD *thd, Alter_info &alter_info, FK_list &foreign_keys);
+
+
 /**
   @brief Helper function for explain_filename
   @param thd          Thread handle
@@ -4657,6 +4661,9 @@ without_overlaps_err:
     }
   }
 
+  /* Check foreign keys */
+  fk_prepare_create_table(thd, *alter_info, foreign_keys);
+
   /* Give warnings for not supported table options */
   extern handlerton *maria_hton;
   if (file->partition_ht() != maria_hton && create_info->transactional &&
@@ -12573,6 +12580,154 @@ bool TABLE_SHARE::fk_handle_create(THD *thd, FK_backup_storage &shares, FK_list
   return false;
 }
 
+
+/** 1. Check referenced fields existence and type compatibility.
+
+    2. Fix foreign and referenced fields case. Fields must be fixed before FRM is
+    written and thus we need to acquire referenced shares now and then later in
+    fk_handle_c6reate() for refs X-locking (and update their FRMs). Note that
+    X-locking is done differently for ALTER so we'd better avoid it here.
+*/
+static
+bool fk_prepare_create_table(THD *thd, Alter_info &alter_info, FK_list &foreign_keys)
+{
+  List_iterator_fast<Create_field> cl_it(alter_info.create_list);
+  mbd::map<Table_name, Share_acquire, Table_name_lt> ref_shares;
+  const bool check_foreign= thd->variables.check_foreign();
+
+  /** Preacquire shares */
+  for (const FK_info &fk: foreign_keys)
+  {
+    if (fk.self_ref())
+      continue;
+    Table_name ref(fk.ref_db(), fk.referenced_table);
+    if (lower_case_table_names)
+      ref .lowercase(thd->mem_root);
+    if (ref_shares.find(ref) != ref_shares.end())
+      continue;
+    TABLE_LIST tl;
+    tl.init_one_table(&ref.db, &ref.name, NULL, TL_IGNORE);
+    Share_acquire sa(thd, tl);
+    if (!sa.share)
+    {
+      if (!check_foreign && thd->is_error() &&
+          thd->get_stmt_da()->sql_errno() == ER_NO_SUCH_TABLE)
+      {
+        // skip non-existing referenced shares, allow CREATE
+        thd->clear_error();
+        continue;
+      }
+      my_error(ER_WRONG_FK_DEF, MYF(0), ref.name.str);
+      return true;
+    }
+    if (!ref_shares.insert(ref, std::move(sa)))
+    {
+      my_error(ER_OUT_OF_RESOURCES, MYF(0));
+      return true;
+    }
+    DBUG_ASSERT(!sa.share);
+  }
+
+  for (FK_info &fk: foreign_keys)
+  {
+    DBUG_ASSERT(fk.foreign_fields.elements == fk.referenced_fields.elements);
+    List_iterator_fast<Lex_cstring> rf_it(fk.referenced_fields);
+    for (Lex_cstring &ff: fk.foreign_fields)
+    {
+      TABLE_SHARE *ref_share= NULL;
+      if (!fk.self_ref())
+      {
+        Table_name ref(fk.ref_db(), fk.referenced_table);
+        auto ref_it= ref_shares.find(ref);
+        if (!check_foreign && ref_it == ref_shares.end())
+          continue;
+        DBUG_ASSERT(ref_it != ref_shares.end());
+        ref_share= ref_it->second.share;
+        DBUG_ASSERT(ref_share);
+      }
+      Lex_cstring &rf= *(rf_it++);
+      Create_field *cf;
+      cl_it.rewind();
+      while ((cf= cl_it++))
+      {
+        if (0 == cmp_ident(cf->field_name, ff))
+          break;
+      }
+      if (!cf)
+      {
+        my_error(ER_WRONG_FK_DEF, MYF(0), ff.str);
+        return true;
+      }
+      // NB: two variants non-self-ref/self-ref, two types Field/Create_field
+      if (ref_share)
+      {
+        Field *ref_field= ref_share->find_field_by_name(rf);
+        if (!ref_field)
+        {
+          if (!check_foreign)
+            continue;
+          my_error(ER_WRONG_FK_DEF, MYF(0), rf.str);
+          return true;
+        }
+        // Do we really need cmp_type() and not result_type() here?
+        if (cf->cmp_type() != ref_field->cmp_type())
+        {
+          if (!check_foreign)
+            continue;
+          my_error(ER_WRONG_FK_DEF, MYF(0), ff.str);
+          return true;
+        }
+        /* NB: case may be different. Let's store correct case. */
+        if (rf.strdup(thd->mem_root, LEX_STRING_WITH_LEN(ref_field->field_name)))
+        {
+          my_error(ER_OUT_OF_RESOURCES, MYF(0));
+          return true;
+        }
+      }
+      else
+      {
+        Create_field *ref_field;
+        cl_it.rewind();
+        while ((ref_field= cl_it++))
+        {
+          if (0 == cmp_ident(ref_field->field_name, rf))
+            break;
+        }
+        // NB: following code is 1-to-1 as if branch, only with Create_field type
+        if (!ref_field)
+        {
+          if (!check_foreign)
+            continue;
+          my_error(ER_WRONG_FK_DEF, MYF(0), rf.str);
+          return true;
+        }
+        // Do we really need cmp_type() and not result_type() here?
+        if (cf->cmp_type() != ref_field->cmp_type())
+        {
+          if (!check_foreign)
+            continue;
+          my_error(ER_WRONG_FK_DEF, MYF(0), ff.str);
+          return true;
+        }
+        /* NB: case may be different. Let's store correct case. */
+        if (rf.strdup(thd->mem_root, LEX_STRING_WITH_LEN(ref_field->field_name)))
+        {
+          my_error(ER_OUT_OF_RESOURCES, MYF(0));
+          return true;
+        }
+      } // else (!ref_share)
+      /* NB: case may be different. Let's store correct case. */
+      if (ff.strdup(thd->mem_root, LEX_STRING_WITH_LEN(cf->field_name)))
+      {
+        my_error(ER_OUT_OF_RESOURCES, MYF(0));
+        return true;
+      }
+    } // for (ff)
+  } // for (fk)
+  return false;
+}
+
+
 /**
   @brief  Used in ALTER TABLE. Prepares data for conducting update on relates shares
   foreign_keys/referenced_keys which is done by fk_handle_alter().
@@ -12806,54 +12961,16 @@ bool Alter_table_ctx::fk_handle_alter(THD *thd)
     // Find prepared FK in fk_list. If ID exists, use it.
     FK_info *fk;
     List_iterator<FK_info> fk_it(new_foreign_keys);
-    if (new_fk.fk->constraint_name.str)
+    DBUG_ASSERT(new_fk.fk->constraint_name.str);
+    while ((fk= fk_it++))
     {
-      while ((fk= fk_it++))
+      if (0 == cmp_ident(fk->foreign_id, new_fk.fk->constraint_name))
       {
-        if (0 == cmp_ident(fk->foreign_id, new_fk.fk->constraint_name))
-        {
-          fk_it.remove();
-          break;
-        }
-      }
-      DBUG_ASSERT(fk);
-    }
-    else
-    {
-      // Otherwise match by parameters.
-      List_iterator_fast<Key_part_spec> col_it;
-      while ((fk= fk_it++))
-      {
-        if (fk->update_method != new_fk.fk->update_opt ||
-            fk->delete_method != new_fk.fk->delete_opt ||
-            fk->foreign_fields.elements != new_fk.fk->columns.elements)
-          continue;
-        if (cmp_table(fk->ref_db(), new_fk.ref.db) ||
-            cmp_table(fk->referenced_table, new_fk.ref.name))
-          continue;
-        col_it.init(new_fk.fk->columns);
-        for (const Lex_cstring &fld: fk->foreign_fields)
-        {
-          Key_part_spec *ref_col= col_it++;
-          if (cmp_ident(fld, ref_col->field_name))
-            break;
-        }
-        if (col_it.peek())
-          continue;
-        col_it.init(new_fk.fk->ref_columns);
-        for (const Lex_cstring &fld: fk->referenced_fields)
-        {
-          Key_part_spec *ref_col= col_it++;
-          if (cmp_ident(fld, ref_col->field_name))
-            break;
-        }
-        if (col_it.peek())
-          continue;
         fk_it.remove();
         break;
-      }  // while ((fk= fk_it++))
-      DBUG_ASSERT(fk);
+      }
     }
+    DBUG_ASSERT(fk);
 
     // Found matched FK. Now let's add it to referenced table.
     FK_info *dst= fk->clone(&ref_share->mem_root);
diff --git a/sql/table.cc b/sql/table.cc
index 4f0028ec7fc..57c55b108b0 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -10209,6 +10209,27 @@ Field *TABLE::find_field_by_name(LEX_CSTRING *str) const
 }
 
 
+Field *TABLE_SHARE::find_field_by_name(const LEX_CSTRING n) const
+{
+  Field **f;
+  if (name_hash.records)
+  {
+    f= (Field**) my_hash_search(&name_hash, (uchar *) n.str, n.length);
+    return f ? *f : NULL;
+  }
+  else
+  {
+    for (f= field; *f; f++)
+    {
+      if ((*f)->field_name.length == n.length &&
+          0 == cmp_ident((*f)->field_name, n))
+        return *f;
+    }
+  }
+  return NULL;
+}
+
+
 bool TABLE::export_structure(THD *thd, Row_definition_list *defs)
 {
   for (Field **src= field; *src; src++)
diff --git a/sql/table.h b/sql/table.h
index c689fb94128..4e207104974 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -732,6 +732,7 @@ struct TABLE_SHARE
   KEY  *key_info;			/* data of keys in database */
   FK_list foreign_keys;
   FK_list referenced_keys;
+  Field *find_field_by_name(const LEX_CSTRING n) const;
   bool fk_handle_create(THD *thd, FK_backup_storage &shares, FK_list *fk_add= NULL);
   bool fk_check_consistency(THD *thd);
   bool referenced_by_foreign_key() const