I am attempting to migrate from MariaDB to MySQL by doing a mysqldump from MariaDB and then restoring it to MySQL (mysql:latest docker container).

I am getting the following error when importing into MySQL:

ERROR 3822 (HY000) at line 172: Duplicate check constraint name 'CONSTRAINT_1'.

If I look at the mysqldump file I can see why this is happening. All boolean columns in my database have a constraints that looks something like:

CONSTRAINT `CONSTRAINT_1` CHECK (`bool_col_1` in (0,1))
CONSTRAINT `CONSTRAINT_2` CHECK (`bool_col_2` in (0,1))
CONSTRAINT `CONSTRAINT_3` CHECK (`bool_col_3` in (0,1))

These constraints were not explicitly created by me but implicitly by Flask-SQLAlchemy (I think).

Notice how the constraint names are incremented starting with CONSTRAINT_1. Well the problem is that each table starts incrementing its constraint names starting with CONSTRAINT_1. Thus the error I am seeing gets thrown when trying to create the second table. According to the MySQL docs, duplicate constraint names are not allowed. Apparently MariaDB allows them.

Is there a way to rename these constraints systematically or an alternative way to migrate the data?

The quantity of tables prohibits manually changing the name of each constraint.

Note: This fiddle tests duplicate constraint names and executes without error on MySQL. However if I run the same commands on a fresh MySQL container, it fails with the duplicate constraint.

  • Is there a way to rename these constraints systematically Of course. For example, create your own pattern. It can be, for example, CONSTRAINT_tablename_digit...
    – Akina
    May 27, 2020 at 17:03
  • @Akina - Perhaps I should have clarified. There are a large number of table that have these constraints such that manually changing them is not a reasonable option. Is there a way to rename them without manually changing each one?
    – noslenkwah
    May 27, 2020 at 17:08
  • 1
    There is no such service in MySQL. But you may parse dump file, find all CREATE TABLE statements and rename their constraints.
    – Akina
    May 27, 2020 at 17:26


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.