Skip to main content

Disable foreign key checks

All Aiven for MySQL® services have foreign key checks enabled by default helping in keeping referential integrity across tables. However, you might want to disable it for a particular session. For example, when migrating to an Aiven for MySQL you may face errors related to foreign key violations similar to:

ERROR 3780 (HY000) at line 11596: Referencing column 'g_id' and referenced column 'g_id' in foreign key constraint 'FK_33b11dcfac6148578da087b07c2f388f' are incompatible.

The following explains how to temporarily disable Aiven for MySQL foreign key checking for the duration of a session.

Prerequisites​

  • The mysqlsh client installed. You can install this by following the MySQL shell installation documentation.
  • An Aiven account with an Aiven for MySQL service running.

Variables​

There are a few variables to substitute when running the commands. To find the values for the substitution, go to Aiven Console > your Aiven for MySQL service > Overview > the Connection information section > the MySQL tab.

VariableDescription
HOSTHostname for MySQL connection
PORTPort for MySQL connection
PASSWORDPassword of your Aiven for MySQL connection
DB_NAMEDatabase Name of your Aiven for MySQL connection

Check the foreign key check flag​

To check the foreign key check flag:

  • Connect to your Aiven for MySQL service with the following command:

    mysql --user avnadmin --password=PASSWORD --host HOST --port PORT DB_NAME

  • Run the following command to check the default configuration for your foreign key checks.

    SHOW VARIABLES LIKE 'foreign_key_checks';

  • Verify that the foreign keys are enabled by default. You can expect to receive the following output:

    +--------------------+-------+
    | Variable_name      | Value |
    +--------------------+-------+
    | foreign_key_checks | ON    |
    +--------------------+-------+
    1 row in set (0.05 sec)

Disable foreign key checks​

To disable the foreign key checks for the session, you give an additional parameter when you connect to your Aiven for MySQL using the mysqlsh:

mysql                   \
  --user avnadmin       \
  --password=PASSWORD   \
  --host HOST           \
  --port PORT DB_NAME   \
  --init-command="SET @@SESSION.foreign_key_checks = 0;"

Once again, we can check the current status of the foreign key checks by running the following:

SHOW VARIABLES LIKE 'foreign_key_checks';

As result, we can see that the foreign key checks are disabled for this session:

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.04 sec)

The same flag works when running a set of commands saved in a file with extension .sql.

VariableDescription
FILENAMEFile which the extension is .sql, for for example, filename.sql

You can paste the following command on your FILENAME:

SHOW VARIABLES LIKE 'foreign_key_checks';

Now you can set the init-command flag to disable the foreign key checks, and run the commands in this file.

mysql                   \
  --user avnadmin       \
  --password=PASSWORD   \
  --host HOST           \
  --port PORT DB_NAME   \
  --init-command="SET @@SESSION.foreign_key_checks = 0;" < FILENAME

More resources​

Read the official documentation to understand possible implications that can happen when disabling foreign key checks in your service.