📋

Description

MySQL Error 1851 indicates that an attempt to add a foreign key constraint failed because the `foreign_key_checks` system variable is currently enabled. This setting prevents operations that might violate referential integrity, often requiring it to be temporarily disabled during schema modifications involving foreign keys.

💬

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`, which enforces referential integrity during DDL operations and prevents the addition of new foreign keys.

⚠️

Default Server Setting

Your MySQL server instance might be configured to have `foreign_key_checks` enabled by default, requiring explicit action to temporarily disable it for foreign key additions.

⚠️

Complex Schema Changes

During advanced database migrations or bulk data operations, `foreign_key_checks` must often be temporarily disabled to prevent conflicts and ensure smooth execution of foreign key additions.

🛠️

Solutions

4 solutions available

1. Temporarily Disable Foreign Key Checks easy

Quickly add foreign keys by disabling checks for the current session.

1

Connect to your MySQL server using your preferred client (e.g., MySQL Workbench, `mysql` command-line client).

2

Execute the following SQL statement to disable foreign key checks for the current session.

SET foreign_key_checks = OFF;

3

Now, add your foreign key constraints. For example, if you are altering an existing table:

ALTER TABLE your_child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES your_parent_table (parent_column);

4

After successfully adding all your foreign keys, re-enable foreign key checks for the current session.

SET foreign_key_checks = ON;

2. Add Foreign Keys During Table Creation easy

Include foreign key definitions directly when creating your tables.

1

When creating your tables, define the foreign key constraints as part of the `CREATE TABLE` statement.

CREATE TABLE parent_table (
    parent_id INT PRIMARY KEY
);

CREATE TABLE child_table (
    child_id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id)
        REFERENCES parent_table(parent_id)
);

2

If you are creating tables within a script, ensure the parent table is created before the child table.

3. Modify Existing Tables with Foreign Keys medium

Add foreign keys to existing tables by disabling checks, adding keys, and re-enabling checks.

1

Connect to your MySQL database.

2

Disable foreign key checks.

SET foreign_key_checks = OFF;

3

Add the foreign key constraint to the child table.

ALTER TABLE your_child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_child_table)
REFERENCES your_parent_table (column_in_parent_table);

4

Re-enable foreign key checks.

SET foreign_key_checks = ON;

5

Verify that the foreign key has been successfully added by describing the table structure.

DESCRIBE your_child_table;

4. Scripting Foreign Key Additions with Error Handling advanced

Automate adding foreign keys in a script with checks to ensure they are added correctly.

1

Create a script (e.g., a shell script) that will execute SQL commands.

#!/bin/bash

DB_USER="your_db_user"
DB_PASSWORD="your_db_password"
DB_NAME="your_db_name"

# Disable foreign key checks
mysql -u$DB_USER -p$DB_PASSWORD $DB_NAME -e "SET foreign_key_checks = OFF;"

2

Add the foreign key statements within the script.

mysql -u$DB_USER -p$DB_PASSWORD $DB_NAME <<EOF
ALTER TABLE your_child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_child_table)
REFERENCES your_parent_table (column_in_parent_table);
EOF

3

Re-enable foreign key checks and add error handling.

mysql -u$DB_USER -p$DB_PASSWORD $DB_NAME -e "SET foreign_key_checks = ON;"

if [ $? -eq 0 ]; then
    echo "Foreign keys added successfully."
else
    echo "Error adding foreign keys. Please check your SQL statements and table structures."
    exit 1
fi

🔗

Related Errors

5 related errors

MySQL Error 1851: Cannot Add Foreign Keys