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
, CONSTRAINTfk_comments_projects1
FOREIGN KEY (project_id
) REFERENCESprojects
(id
) ON DELETE NO
ACTION ON UPDATE NO ACTION)
У меня немного странная проблема. Я пытаюсь добавить внешний ключ к одной таблице, которая ссылается на другую, но по какой-то причине она не работает. Имея мои ограниченные знания 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
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.
The MySQL ERROR 1452
happens when you try to execute a data manipulation query into a table that has one or more failing foreign key constraints.
The cause of this error is the values you’re trying to put into the table are not available in the referencing (parent) table.
Let’s see an example of this error with two MySQL tables.
Suppose you have a Cities
table that contains the following data:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
+----+------------+
Then, you create a Friends
table to keep a record of people you know who lives in different cities.
You reference the id
column of the Cities
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 `Cities` (`id`)
)
In the code above, a CONSTRAINT
named friends_ibfk_1
is created for the city_id
column, referencing the id
column in the Cities
table.
This CONSTRAINT
means that only values recoded in the id
column can be inserted into the city_id
column.
(To avoid confusion, I have omitted the id
column from the Friends
table. In real life, You may have an id
column in both tables, but a FOREIGN KEY
constraint will always refer to a different table.)
When I try to insert 5
as the value of the city_id
column, I will trigger the error as shown below:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 5);
The response from MySQL:
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 `cities` (`id`))
As you can see, the error above even describes which constraint you are failing from the table.
Based on the Cities
table data above, I can only insert numbers between 1
to 4
for the city_id
column to make a valid INSERT
statement.
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('John', 1);
-- Query OK, 1 row affected (0.00 sec)
The same error will happen when I try to update the Friends
row with a city_id
value that’s not available.
Take a look at the following example:
UPDATE `Friends` SET city_id = 5 WHERE `firstName` = 'John';
-- ERROR 1452 (23000): Cannot add or update a child row
There are two ways you can fix the ERROR 1452
in your MySQL database server:
- You add the value into the referenced table
- You disable the
FOREIGN_KEY_CHECKS
in your server
The first option is to add the value you need to the referenced table.
In the example above, I need to add the id
value of 5
to the Cities
table:
INSERT INTO `Cities` VALUES (5, 'Liverpool');
-- Cities table:
+----+------------+
| id | city_name |
+----+------------+
| 1 | York |
| 2 | Manchester |
| 3 | London |
| 4 | Edinburgh |
| 5 | Liverpool |
+----+------------+
Now I can insert a new row in the Friends
table with the city_id
value of 5
:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Susan', 5);
-- Query OK, 1 row affected (0.00 sec)
Disabling the foreign key check
The second way you can fix the ERROR 1452
issue is to disable the FOREIGN_KEY_CHECKS
variable in your MySQL server.
You 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 your table(s) before inserting or updating.
You 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 you can INSERT
or UPDATE
rows in your table without triggering a foreign key constraint fails
:
INSERT INTO `Friends` (`firstName`, `city_id`) VALUES ('Natalia', 8);
-- Query OK, 1 row affected (0.01 sec)
UPDATE `Friends` SET city_id = 17 WHERE `firstName` = 'John';
-- Query OK, 1 row affected (0.00 sec)
-- Rows matched: 1 Changed: 1 Warnings: 0
After you’re done with the manipulation query, you 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;
But please be warned that turning off your FOREIGN_KEY_CHECKS
variable will cause the city_id
column to reference a NULL
column in the cities
table.
It may cause problems when you need to perform a JOIN
query later.
Now you’ve learned the cause of ERROR 1452
and how to resolve this issue in your MySQL database server. Great work! 👍
Решения вопроса 1
@Rsa97
Для правильного вопроса надо знать половину ответа
Убедиться, то в связываемом поле дочерней таблицы нет значений, отсутствующих в связываемом поле родительской таблицы и оба этих поля имеют абсолютно одинаковый тип.
И на будущее — не вставляйте код или сообщения об ошибках картинками. Копируйте текст.
Пригласить эксперта
Похожие вопросы
-
Показать ещё
Загружается…
22 сент. 2023, в 03:45
2000 руб./в час
22 сент. 2023, в 01:22
10000 руб./за проект
22 сент. 2023, в 00:39
30000 руб./за проект