Server Management Services<\/a>, we at Skynats provide answers for all kinds of queries, both big and small.<\/p>\n\n\n\nLet’s look at how our support staff can assist clients who are experiencing MySQL Error 1452.<\/p>\n\n\n\n
How do I fix MySQL Error 1452?<\/h2>\n\n\n\n
This error typically happens when we attempt to run a data manipulation query into a table that contains one or more broken foreign key constraints.<\/p>\n\n\n\n
Why does MySQL Error 1452 occur?<\/h3>\n\n\n\n
The values we are attempting to insert into the table are not present in the referencing (parent) table, which is the root cause of this error.<\/p>\n\n\n\n
A Foreign Key is a table column that is referenced from another table.<\/p>\n\n\n\n
Consider the table City, which contains the name and ID of a city.<\/p>\n\n\n\n
Additionally, there are table Buddies for keeping track of people we know who reside in different cities.<\/p>\n\n\n\n
As stated below, the id column of the City table must be used as the FOREIGN KEY of the city_id column in the friends’ table:<\/p>\n\n\n\n
CREATE TABLE friends (\nfirstName varchar(255) NOT NULL,\ncity_id int unsigned NOT NULL,\nPRIMARY KEY (firstName),\nCONSTRAINT friends_ibfk_1\nFOREIGN KEY (city_id) REFERENCES City (id)\n)<\/code><\/pre>\n\n\n\nIn the preceding code, a CONSTRAINT named buddies_ibfk_1 is created for the city_id column, which refers to the id column of the City table.<\/p>\n\n\n\n
This CONSTRAINT states that the city id column can only accept values from the id column.<\/p>\n\n\n\n
The following error will appear if we attempt to insert a value into the city_id column that does not already exist in the id column:<\/p>\n\n\n\n
ERROR 1452 (23000): Cannot add or update a child row:\na foreign key constraint fails\n(test_db.friends, CONSTRAINT friends_ibfk_1\nFOREIGN KEY (city_id) REFERENCES city (id))<\/code><\/pre>\n\n\n\nHow do you fix it?<\/h4>\n\n\n\n
Let’s look at what our support technicians did to fix it:<\/p>\n\n\n\n
The MySQL database server ERROR 1452 can be fixed in one of two ways:<\/p>\n\n\n\n
\n- First, enter the value in the associated table.<\/li>\n\n\n\n
- Afterward, turn off FOREIGN_KEY_CHECKS on the server<\/li>\n<\/ol>\n\n\n\n
\n- Include the value in the table that is mentioned.<\/li>\n<\/ol>\n\n\n\n
The first choice is to add the necessary value to the table that is being referenced.<\/p>\n\n\n\n
In the preceding example, add the necessary id value to the City table.<\/p>\n\n\n\n
The city_id value we previously inserted can now be used to add a new row to the Buddies table.<\/p>\n\n\n\n
Disabling the foreign key check<\/p>\n\n\n\n
\n- In the MySQL server, disable the FOREIGN_KEY_CHECKS variable.<\/li>\n<\/ol>\n\n\n\n
Run the following command to see if the variable is active or not:<\/p>\n\n\n\n
SHOW GLOBAL VARIABLES LIKE \u2018FOREIGN_KEY_CHECKS\u2019;\n\n \u2014 +\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+\n\u2014 | Variable_name | Value |\n\u2014 +\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+\n\u2014 | foreign_key_checks | ON |\n\u2014 +\u2014\u2014\u2014\u2014\u2014\u2014\u2013+\u2014\u2014-+<\/code><\/pre>\n\n\n\nBefore inserting or updating, this variable instructs MySQL to verify any foreign key constraints that have been added to our table(s).<\/p>\n\n\n\n
The variable can be disabled for the current session or globally:<\/p>\n\n\n\n
\u2014 set for the current session:\nSET FOREIGN_KEY_CHECKS=0;<\/code><\/pre>\n\n\n\n\u2014 set globally:\nSET GLOBAL FOREIGN_KEY_CHECKS=0;<\/code><\/pre>\n\n\n\nWe can now INSERT or UPDATE rows in our table without fear of breaking a foreign key constraint.<\/p>\n\n\n\n
When the manipulation query is finished, we can make the FOREIGN_KEY_CHECKS active once more by setting its value to 1:<\/p>\n\n\n\n
\u2014 set for the current session:\nSET FOREIGN_KEY_CHECKS=1;<\/code><\/pre>\n\n\n\n\u2014 set globally:\nSET GLOBAL FOREIGN_KEY_CHECKS=1;<\/code><\/pre>\n\n\n\nThe city_id column will refer to a NULL column in the City table if the FOREIGN_KEY_CHECKS variable is disabled.<\/p>\n\n\n\n
In the future, if we need to run a JOIN query, it might become problematic.<\/p>\n\n\n\n
Are you looking for an answer to another query? Contact<\/a> our technical support team.<\/p>\n","protected":false},"excerpt":{"rendered":"Looking for a solution to MySQL Error 1452? You can rely on us. As a part of our Server Management Services, we at Skynats provide answers for all kinds of queries, both big and small. Let’s look at how our support staff can assist clients who are experiencing MySQL Error 1452. How do I fix […]<\/p>\n","protected":false},"author":8,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,257],"tags":[701],"class_list":["post-9728","post","type-post","status-publish","format-standard","hentry","category-blog","category-mysql","tag-mysql"],"_links":{"self":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts\/9728","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/comments?post=9728"}],"version-history":[{"count":0,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts\/9728\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/media?parent=9728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/categories?post=9728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/tags?post=9728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}