创建表时添加外键不成功的问题

[evillier]

我需要建2张表，一张主表sub_device设备表，主键是id。 另一张表sub_device_data_log 设备数据日志表，他的sub_device_id需要关联到sub_device主表的id字段。
CREATE TABLE sub_device_data_log (
id int(11) NOT NULL AUTO_INCREMENT,
create_time datetime(0) NOT NULL COMMENT '创建时间',
data_value decimal(14, 6) NOT NULL DEFAULT 0.000000 COMMENT '数值',
sub_device_id bigint NOT NULL COMMENT '子设备id',
PRIMARY KEY (id) USING BTREE,
CONSTRAINT sub_device_id_key FOREIGN KEY (sub_device_id) REFERENCES sub_device (id) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci
DBPARTITION BY MOD_HASH(id) DBPARTITIONS 2 TBPARTITION BY MOD_HASH(id) TBPARTITIONS 1;

但是执行后没成功，看日志提示Caused by: java.sql.SQLException: Cannot add foreign key constraint。
如果直接去实际的物理库执行建表语句需要将外键的sub_device主表名称改为sub_device_1或者sub_device_0才能成功。