📋

Description

This error occurs in MariaDB when you attempt to add or modify foreign key constraints on a table while the `foreign_key_checks` system variable is enabled. MariaDB requires `foreign_key_checks` to be temporarily disabled during certain schema alterations involving foreign keys to ensure data integrity and avoid validation failures.

💬

Error Message

Adding foreign keys needs foreign_key_checks=OFF

🔍

Known Causes

3 known causes

⚠️

Foreign Key Checks Enabled

The `foreign_key_checks` system variable is currently set to `ON`, preventing structural changes that involve foreign keys without explicit disabling.

⚠️

Automated Script Conflict

An automated script, migration tool, or ORM is attempting to modify foreign keys without first disabling `foreign_key_checks`, leading to this validation error.

⚠️

Unawareness of Requirement

The database user or administrator is not aware that MariaDB requires `foreign_key_checks` to be temporarily turned `OFF` for certain foreign key operations.

🛠️

Solutions

3 solutions available

1. Temporarily Disable Foreign Key Checks easy

Disable foreign key checks to allow adding constraints, then re-enable them.

1

Connect to your MariaDB instance using a client like the MariaDB command-line client or a GUI tool.

2

Disable foreign key checks for the current session.

SET foreign_key_checks = 0;

3

Execute the `ALTER TABLE` statement(s) that add your foreign keys.

ALTER TABLE child_table ADD CONSTRAINT fk_parent_id FOREIGN KEY (parent_id) REFERENCES parent_table(id);

4

Re-enable foreign key checks to ensure data integrity.

SET foreign_key_checks = 1;

2. Set Global Foreign Key Checks to Off (Use with Caution) medium

Disable foreign key checks globally, which affects all connections.

1

Connect to your MariaDB instance with administrative privileges.

2

Modify the `foreign_key_checks` system variable globally. This change will persist until the server restarts.

SET GLOBAL foreign_key_checks = 0;

3

Execute the `ALTER TABLE` statement(s) to add your foreign keys. This can be done in a separate session if preferred.

ALTER TABLE child_table ADD CONSTRAINT fk_parent_id FOREIGN KEY (parent_id) REFERENCES parent_table(id);

4

Re-enable foreign key checks globally.

SET GLOBAL foreign_key_checks = 1;

5

For the change to be permanent across server restarts, you need to update the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`). Add or modify the following line under the `[mysqld]` section:

[mysqld]
foreign_key_checks = 0

6

Restart the MariaDB service for the configuration file changes to take effect.

sudo systemctl restart mariadb

3. Ensure Data Integrity Before Adding Foreign Keys advanced

Pre-validate that all existing data in the child table adheres to the foreign key constraint.

1

Identify the columns involved in the foreign key relationship.

2

Write a query to check for any rows in the child table where the foreign key column's value does not exist in the parent table's referenced column.

SELECT c.* FROM child_table c LEFT JOIN parent_table p ON c.parent_id = p.id WHERE p.id IS NULL;

3

If the query returns any rows, you must either delete these orphaned rows or update them to reference a valid parent row before attempting to add the foreign key.

DELETE FROM child_table WHERE parent_id NOT IN (SELECT id FROM parent_table);

4

Once data integrity is confirmed, you can add the foreign key without needing to disable checks.

ALTER TABLE child_table ADD CONSTRAINT fk_parent_id FOREIGN KEY (parent_id) REFERENCES parent_table(id);

🔗

Related Errors

5 related errors

MariaDB Error 1851: Cannot Add Foreign Keys