0

I'm trying to import database tables from a file of table definitions. Near the top, there is a line setting foreign_key_checks to 0, so that no errors would be thrown because of tables that reference other not-yet-created tables.

With an older version of MariaDB, this all worked fine.

However, with MariaDB 10.6 or later, when creating a table that has a check constraint referencing a table that does not yet exist, it throws an error even when this setting is set to 0.

Just a note here, that creating actual foreign keys on a new table that point to a non-existing table do not fail, only check constraints fail.

The SQL file was created with Adminer.

One solution could be to manually comment out the check constraints in the "create table" statements, and add them at the bottom of the file in "alter table" statements, but I would prefer to not do this, as it requires manual intervention.

Any ideas for workarounds or solutions?

Magmatic
  • 1,754
  • 3
  • 19
  • 34
  • 1
    As a foreign key and a check constraint are two different things, you probably want to have a look at the check_constraint_checks parameter as well – NickW Jul 24 '23 at 16:34
  • 1
    Not clear to me, but maybe that setting only affects insertion of data, not table definitions. You could easily test by creating all tables first, and insert data later. – The Impaler Jul 24 '23 at 17:31
  • @TheImpaler That setting only applies to FK constraints (and is working as expected according to the questionner). There’s another setting that is specific to Check constraints – NickW Jul 24 '23 at 17:35
  • Give us an example of the unwanted behavior. – jarlh Jul 24 '23 at 19:04

1 Answers1

0

The problem I was actually encountering was this bug:

https://jira.mariadb.org/browse/MDEV-31769

So maybe the work-around is to get that bug fixed.

Magmatic
  • 1,754
  • 3
  • 19
  • 34