Flyway & MariaDB: SQLException Foreign key constraint is incorrectly formed

Question

I'm currently trying to switch from Hibernate generating my ddl scheme to Flyway migrations. I generated my V1__Initial.sql script by this means. When I run it on an in-memory H2 database everything works fine. But when I try to run it on MariaDB I get the following exception:

Migration V1__Initial.sql failed
--------------------------------
SQL State  : HY000
Error Code : 1005
Message    : (conn=130) Can't create table `booking`.`booking_cancelled_event` (errno: 150 "Foreign key constraint is incorrectly formed")
Location   : db/migration/V1__Initial.sql (C:\code\ajt\backend\target\classes\db\migration\V1__Initial.sql)
Line       : 463
Statement  : alter table booking_cancelled_event
   add constraint FKnxiyj0m730pl9ol2y4qng7577
   foreign key (cancelled_booking_id)
   references cancelled_internal_booking

The corresponding part from the V1__Initial.sql:

alter table booking_cancelled_event
   add constraint FKnxiyj0m730pl9ol2y4qng7577
   foreign key (cancelled_booking_id)
   references cancelled_internal_booking;

Output from SHOW ENGINE InnoDB STATUS;:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-06-23 21:07:30 0x1974 Error in foreign key constraint of table `xxx`.`booking_cancelled_event`:
Alter  table `booking`.`booking_cancelled_event` with foreign key constraint failed. Parse error in '
   foreign key (cancelled_booking_id)
   references cancelled_internal_booking' near '
   references cancelled_internal_booking'.

Versions:

MariaDB 10.4
Spring Boot 2.1.5.RELEASE
Flyway 5.2.4

Either run the `CREATE TABLE` statements in the proper order (for FKs), or disable, then reenable FKs. — Rick James, Jun 23 '19 at 19:55
@RickJames is the scipt not being executed in the same order as they are written in the script? The first statements are the create table ones and the foreign keys are defined later! — times29, Jun 23 '19 at 20:07

score 3 · Accepted Answer · answered Jun 23 '19 at 20:26

According to the ALTER TABLE page of the MariaDB documentation the syntax of the ADD CONSTRAINT should be:

ADD [CONSTRAINT [symbol]]
    FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
    reference_definition

and according to the CREATE TABLE page of the MariaDB documentation, the syntax of the reference_definition should be:

REFERENCES tbl_name (index_col_name,...)
  [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
  [ON DELETE reference_option]
  [ON UPDATE reference_option]

So the parse error you encounter is most probably because you miss a (index_col_name,...) after the cancelled_internal_booking table name.

The mistake I made was to run the jpa ddl creation against an in-memory H2 and then I tried to apply the commands in my Flyway script against the MaraiDB. — times29, Jun 29 '19 at 16:13

Flyway & MariaDB: SQLException Foreign key constraint is incorrectly formed

1 Answers1