Код ошибки 1452

I am trying to insert values into my comments table and I am getting a error. Its saying that I can not add or update child row and I have no idea what that means.

my schema looks something like this

-- ----------------------------
-- Table structure for `comments`
-- ----------------------------
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
  `id` varchar(36) NOT NULL,
  `project_id` varchar(36) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `task_id` varchar(36) NOT NULL,
  `data_type_id` varchar(36) NOT NULL,
  `data_path` varchar(255) DEFAULT NULL,
  `message` longtext,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_comments_users` (`user_id`),
  KEY `fk_comments_projects1` (`project_id`),
  KEY `fk_comments_data_types1` (`data_type_id`),
  CONSTRAINT `fk_comments_data_types1` FOREIGN KEY (`data_type_id`) REFERENCES `data_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_comments_projects1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_comments_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf32;

-- ----------------------------
-- Records of comments
-- ----------------------------

-- ----------------------------
-- Table structure for `projects`
-- ----------------------------
DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `id` varchar(36) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `title` varchar(45) DEFAULT NULL,
  `description` longtext,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_projects_users1` (`user_id`),
  CONSTRAINT `fk_projects_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf32;

-- ----------------------------
-- Records of projects
-- ----------------------------
INSERT INTO `projects` VALUES ('50dcbc72-3410-4596-8b71-0e80ae7aaee3', '50dcbc5c-d684-40bf-9715-0becae7aaee3', 'Brand New Project', 'This is a brand new project', '2012-12-27 15:24:02', '2012-12-27 15:24:02');

and the mysql statement I am trying to do looks something like this

INSERT INTO `anthonyl_fbpj`.`comments` (`project_id`, `user_id`, `task_id`, `data_type_id`, `message`, `modified`, `created`, `id`) 
VALUES ('50dc845a-83e4-4db3-8705-5432ae7aaee3', '50dcbc5c-d684-40bf-9715-0becae7aaee3', '1', '50d32e5c-abdc-491a-a0ef-25d84e9f49a8', 'this is a test', '2012-12-27 19:20:46', '2012-12-27 19:20:46', '50dcf3ee-8bf4-4685-aa45-4eb4ae7aaee3')

the error I get looks like this

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or
update a child row: a foreign key constraint fails
(anthonyl_fbpj.comments, CONSTRAINT fk_comments_projects1
FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE NO
ACTION ON UPDATE NO ACTION)

I’m receiving an error when I attempt to create two tables. There was a multivalued dependency, so I separated the tables and came up with this:

CREATE TABLE NAME (
NameID      Integer             NOT NULL AUTO_INCREMENT,
Name        varChar(255)        NOT NULL,
CONSTRAINT  NAME_PK             PRIMARY KEY(NameID)
);

CREATE TABLE PHONE (
NameID      Integer             NOT NULL,
PhoneNumber varChar(15)         NOT NULL,
NumType     varChar(5)          NOT NULL,
CONSTRAINT  PHONE_FK            FOREIGN KEY(NameID)
    REFERENCES NAME(NameID),
CONSTRAINT  PHONE_PK            PRIMARY KEY(NameID)
);

But when attempting to add values with this code:

INSERT INTO NAME (NameID, Name) VALUES (default, 'John Doe');
INSERT INTO PHONE (NameID, PhoneNumber, NumType) VALUES (default, '706-782-4719', 'Home');

I receive the infamous 1452 error:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`phone_mcneill`.`PHONE`, CONSTRAINT `PHONE_FK` FOREIGN KEY (`NameID`) REFERENCES `NAME` (`NameID`))

I am not entirely sure what this means as I have NameID autoincrementing in the first table. I can’t have it auto_increment in the second one as well as it’s a foreign key, correct? Thanks in advance for the help.

У меня немного странная проблема. Я пытаюсь добавить внешний ключ к одной таблице, которая ссылается на другую, но по какой-то причине она не работает. Имея мои ограниченные знания MySQL, единственное, что может быть подозрительным, это наличие внешнего ключа в другой таблице, ссылающейся на ту, которую я пытаюсь ссылаться.

Вот изображение моих связей в таблице, сгенерированное с помощью phpMyAdmin:
Отношения

Я выполнил запрос SHOW CREATE TABLE для обеих таблиц, sourcecodes_tags — это таблица с внешним ключом, sourcecodes — ссылочная таблица.

CREATE TABLE `sourcecodes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `language_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 `title` varchar(40) CHARACTER SET utf8 NOT NULL,
 `description` text CHARACTER SET utf8 NOT NULL,
 `views` int(11) unsigned NOT NULL,
 `downloads` int(11) unsigned NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `language_id` (`language_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `sourcecodes_tags` (
 `sourcecode_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`sourcecode_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Было бы здорово, если бы кто-нибудь мог рассказать мне, что здесь происходит, у меня не было формального обучения или чего-то еще с MySQL:)

Спасибо.

Изменить: Это код, который генерирует ошибку:

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

Wondering how to resolve MySQL Error 1452? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Microsoft SQL Server Support Services.

Let’s take a look at how our Support Team is ready to help customers with MySQL Error 1452.

How to resolve MySQL Error 1452?

Usually, this error occurs when we try to execute a data manipulation query into a table that has one or more failing foreign key constraints.

What causes MySQL Error 1452?

The cause of this error is the values we are trying to put into the table are not available in the referencing (parent) table.

When a column of a table is referenced from another table, it is called Foreign Key.

For example, consider a table City that contains the name of a city and its ID.

Also, there is another table Buddies to keep a record of people that we know who lives in different cities.

We have to reference the id column of the City table as the FOREIGN KEY of the city_id column in the friends table as follows:

CREATE TABLE friends (
firstName varchar(255) NOT NULL,
city_id int unsigned NOT NULL,
PRIMARY KEY (firstName),
CONSTRAINT friends_ibfk_1
FOREIGN KEY (city_id) REFERENCES City (id)
)

In the code above, a CONSTRAINT named buddies_ibfk_1 is created for the city_id column, referencing the id column in the City table.

This CONSTRAINT means that only values in the id column can be inserted into the city_id column.

If we try to insert a value that is not present in id column into the city_id column, it will trigger the error as shown below:

ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
(test_db.friends, CONSTRAINT friends_ibfk_1
FOREIGN KEY (city_id) REFERENCES city (id))

How to resolve it?

Today, let us see the steps followed by our Support Techs to resolve it:

There are two ways to fix the ERROR 1452 in MySQL database server:

1. Firstly, add the value into the referenced table
2. Then, disable the FOREIGN_KEY_CHECKS in the server

1. Add the value into the referenced table

The first option is to add the value we need to the referenced table.

In the example above, add the required id value to the City table.

Now we can insert a new row in the Buddies table with the city_id value that we inserted.

Disabling the foreign key check

2. Disable the FOREIGN_KEY_CHECKS variable in MySQL server.

We can check whether the variable is active or not by running the following query:

SHOW GLOBAL VARIABLES LIKE ‘FOREIGN_KEY_CHECKS’;

 — +——————–+——-+
— | Variable_name | Value |
— +——————–+——-+
— | foreign_key_checks | ON |
— +——————–+——-+

This variable causes MySQL to check any foreign key constraint added to our table(s) before inserting or updating.

We can disable the variable for the current session only or globally:

— set for the current session:
SET FOREIGN_KEY_CHECKS=0;

— set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=0;

Now we can INSERT or UPDATE rows in our table without triggering a foreign key constraint fails.

After we are done with the manipulation query, we can set the FOREIGN_KEY_CHECKS active again by setting its value to 1:

— set for the current session:
SET FOREIGN_KEY_CHECKS=1;

— set globally:
SET GLOBAL FOREIGN_KEY_CHECKS=1;

Turning off FOREIGN_KEY_CHECKS variable will cause the city_id column to reference a NULL column in the City table.

It may cause problems when we need to perform a JOIN query later.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrated how to resolve MySQL Error 1452.