Kevin Gravell Kevin Gravell - 6 months ago 24
MySQL Question

How truncate a table in MySQL that is using ManyToMany field

I have a table in MySQL which have a ManyToMany field and I want truncate the table, but when I try it, I obtain the following error:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint ...


I'm using Symfony with Doctrine but if it is possible, I'm interested in learn how to do it through console

class Project {

/**
* @ORM\ManyToMany(targetEntity="Shipping", mappedBy="projects")
**/
private $employee;
}

class Employee{

/**
* @ORM\ManyToMany(targetEntity="Product", inversedBy="employee")
* @ORM\JoinTable(name="middle_table")
**/
protected $projects;
}

Answer

Foreign key means that you have two table and each update must be compatible with the table referred to by the foreign key constraint.

Posible solution is here: How do I truncate tables properly?

SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checking.
TRUNCATE TABLE forums;
TRUNCATE TABLE dates;
TRUNCATE TABLE remarks;
SET FOREIGN_KEY_CHECKS = 1; -- Enable foreign key checking.