MySQL is a potent tool for designing and building a website, but you often make severe errors when you approach this tool. Among them, “MySQL ERROR 1452 a foreign key constraint fails” is one of the most common errors. Check out this article to find out how to deal with it.
Why does the error “MySQL ERROR 1452 a foreign key constraint fails” occur?
In MySQL, there is a property called Foreign Key. The Foreign Key is used to increase referentiality in the MySQL database. A Foreign Key means that a value in one table must appear in another. The reference table is called the parent table, and the table containing the foreign key is called the child table.
For example, I have a class table containing the name and id of those classes. Then I create a table students regarding that class table with a foreign key of class_id, and the student table will look like this:
CREATE TABLE students (
firstName varchar(255) NOT NULL,
class_id int unsigned NOT NULL,
PRIMARY KEY (firstName),
CONSTRAINT students _ibfk_1
FOREIGN KEY (class_id) REFERENCES class (id)
)Continue, I try to insert a value that is not in the id column into the class_id column, and it throws an error like this:
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
(test_db.students, CONSTRAINT students _ibfk_1
FOREIGN KEY (class_id) REFERENCES class (id))
The cause of the error “MySQL ERROR 1452 a foreign key constraint fails” is when you put a value in the table, but the value is not available in the reference table. In other words, you enter an invalid value when Foreign Key is constrained.
How to resolve the “MySQL ERROR 1452 a foreign key constraint fails” error?
Option 1: Add value to the referenced table
The easiest way to fix this error is to add a value to your referencing table. In the above example, in the class table, add the id value that you need to use in the students table with the following syntax:
INSERT INTO table_name VALUES (value1,value2,value3);Parameter:
- value1, value2, and value3: are the values of column 1, column 2, and column 3, respectively.
Or, more explicitly:
INSERT INTO table_name (column1,column2,column3)
VALUES (value1,value2,value3);
Parameter:
- column1, column2, and column3: are the names of column 1, column 2, and column 3, respectively.
- value1, value2, and value3: are the values of column 1, column 2, and column 3, respectively.
Note: For this syntax, you must ensure the number of columns equals the number of values. Otherwise, when running the command will get an error.
Option 2: Disable the FOREIGN_KEY_CHECKS variable in the MySQL server
MySQL supports checking foreign key variables and allows us to turn them on or off.
Check as follows:
SHOW GLOBAL VARIABLES LIKE 'FOREIGN_KEY_CHECKS'Output:
— +——————–+——-+
— | Variable_name | Value |
— +——————–+——-+
— | foreign_key_checks | ON |
— +——————–+——-+
Next, turn off the operation of the foreign key and proceed to insert whatever data you want without causing an error:
SET GLOBAL FOREIGN_KEY_CHECKS=0;Finally, return the foreign key’s state to its original state:
SET GLOBAL FOREIGN_KEY_CHECKS=1;So you can insert more data into the reference table without causing the error “MySQL ERROR 1452 a foreign key constraint fails”.
Summary
MySQL is a pretty intuitive tool to use, and while it’s not too difficult, it’s not easy to master either. The error “MySQL ERROR 1452 a foreign key constraint fails” is an example above. Let’s start learning from the basics and fixing common mistakes to master them.
Maybe you are interested:
My name is Tom Joseph, and I work as a software engineer. I enjoy programming and passing on my experience. C, C++, JAVA, and Python are my strong programming languages that I can share with everyone. In addition, I have also developed projects using Javascript, html, css.
Job: Developer
Name of the university: UTC
Programming Languages: C, C++, Javascript, JAVA, python, html, css
