1452 sql ошибка

I’m having a bit of a strange problem. I’m trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there is a foreign key on a different table referencing the one I am trying to reference.

I’ve done a SHOW CREATE TABLE query on both tables, sourcecodes_tags is the table with the foreign key, sourcecodes is the referenced table.

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

This is the code that generates the error:

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

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)

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.

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:

• MySQL Command Not Found
• How To MySQL Split String By Delimiter

В ситуациях, когда необходимо совершать действия над таблицей, которая уже содержит в себе какие-либо данные, может возникнуть неприятная ошибка: «Mysql error 1452 — Cannot add or update a child row: a foreign key constraint fails«.

Возникает она вследствие того, что мы пытаемся изменить существующую запись таким образом, что нарушается целостность.

Например, присвоение полю ключа, который не существует в родительской таблице. Или создание нового внешнего ключа для поля, которое не должно быть NULL, в таблице, которая уже содержит некоторое количество записей. Последняя проблема у меня и возникла. Необходимо было решение, и оно было найдено.

Решение простое как пять копеек. Нам необходимо избавиться от NULL-значений в поле, на которое навешивается внешний ключ.

Предположим, что вы уже отредактировали схему соответствующим образом и она имеет примерно следующее содержание:

Human:
  columns:
    name:              { type: string(255), notnull: true }
    human_status_id:   { type: integer, notnull: true }
  relations:
    HumanStatus:
      local: human_status_id
      foreign: id
      onDelete: CASCADE

HumanStatus:
  columns:
    name:            { type: string,  notnull: true  }

После успешной генерации миграций должны появиться два файла миграций: один будет содержать создание таблиц, а вторая создание связей между ними. Именно второй файл мы и будем редактировать. Нам это никто не запрещает.

Итак, как я уже сказал, нам необходимо избавиться от NULL-значений. Для этого мы поправим вторую миграцию примерно таким образом:

public function up()
    {
        $conn = Doctrine_Manager::getInstance()->getCurrentConnection();

        $oHumanStatus = new HumanStatus();
        $oHumanStatus->setName('Temp');
        $oHumanStatus->save();

        Doctrine_Query::create()
            ->update()
            ->from('Human')
            ->set('human_status_id', $oHumanStatus->getId())
            ->execute();
        
        $this->createForeignKey('human', 'human_human_status_id_human_status_id', array(
             'name' => 'human_human_status_id_human_status_id',
             'local' => 'human_status_id',
             'foreign' => 'id',
             'foreignTable' => 'human_status',
             'onUpdate' => '',
             'onDelete' => 'CASCADE',
             ));
        $this->addIndex('human', 'human_human_status_id', array(
             'fields' => 
             array(
              0 => 'human_status_id',
             ),
             ));
    }

Думаю, основная идея понятна. Если нет нужды в создании новой записи в таблице HumanStatus, можно поискать уже существующие. Все зависит от вашей ситуации.

Далее накатываем обе миграции. Все должно пройти успешно. Если нет — читайте мануалы дальше. Возможно, это не ваш случай.

P.S. Не оставляйте die() в up() или down() методах миграции. Иначе она не накатится.

Автор: Артур Минимулин ⚫ 6 мая 2013 г. ⚫ Тэги: php, Symfony, Doctrine, MySQL