The below test case shows that you can create an ibd file without an frm file using sql and foreign keys:
drop database if exists bug_fk;
|
create database bug_fk;
|
use bug_fk;
|
CREATE TABLE b (
|
b int unsigned NOT NULL,
|
d1 datetime NOT NULL,
|
PRIMARY KEY (b,d1)
|
) ENGINE=InnoDB;
|
|
CREATE TABLE c (
|
b int unsigned NOT NULL,
|
d1 datetime NOT NULL,
|
d2 datetime NOT NULL,
|
PRIMARY KEY (b,d1),
|
CONSTRAINT b_fk FOREIGN KEY (b) REFERENCES b (b)
|
) ENGINE=InnoDB;
|
set foreign_key_checks = 0;
|
DROP TABLE IF EXISTS b;
|
CREATE TABLE b (
|
b bigint unsigned NOT NULL,
|
d1 date NOT NULL,
|
PRIMARY KEY (b,d1)
|
) ENGINE=InnoDB;
|
|
DROP TABLE IF EXISTS d;
|
|
CREATE TABLE d (
|
b bigint unsigned NOT NULL,
|
d1 date NOT NULL,
|
PRIMARY KEY (b,d1),
|
CONSTRAINT bd_fk FOREIGN KEY (b) REFERENCES b (b)
|
) ENGINE=InnoDB;
|
|
set foreign_key_checks = 1;
|
CREATE TABLE b (
|
b bigint unsigned NOT NULL,
|
d1 date NOT NULL,
|
PRIMARY KEY (b,d1)
|
) ENGINE=InnoDB;
|
set foreign_key_checks=0;
|
drop table c;
|
drop table d;
|
create table b(id int);
|
Check https://bugs.mysql.com/bug.php?id=81444 and https://bugs.launchpad.net/percona-server/+bug/1582501 for more details (note that -f flag is NOT needed in case of mysql client from MariaDB, as it continues to execute statements in case of SQL error by default).
Bug
Critical
