How truncate a table in MySQL that is using ManyToMany field

Question

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;
}

Please provide related entities – Denis Alimov May 29 '16 at 18:22 — Denis Alimov, May 29 '16 at 18:22
I updated it my question – Kevin Gravell May 29 '16 at 18:36 — Kevin Gravell, May 29 '16 at 18:36
Through Symfony's console or MySQL's one? – Jakub Matczak May 30 '16 at 07:03 — Jakub Matczak, May 30 '16 at 07:03

score 2 · Accepted Answer · edited May 23 '17 at 12:10

2

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.

edited May 23 '17 at 12:10

Community

1
1

answered May 29 '16 at 18:29

v1000

341
3
14

Yes, I have two different classes and an intermediate table. But how can I truncate the table? In my case, I want truncate my `projects` table – Kevin Gravell May 29 '16 at 18:38
Okay, perfect. Thanks you so much. It has sense truncate first child tables and later parent tables – Kevin Gravell May 29 '16 at 18:48

score 0 · Answer 2 · answered May 29 '16 at 18:49

Now your foreign key in middle_table does not allow you to delete records from Projects table. In other words you have a link to Project in middle_table, it does not give a chance to delete row from Project. So you should change definition of your foreign key to allow deletion, you can set to null the link or make cascade delete. I prefer second option, so change annotation to following, it should allow you to delete rows in Projects table, it also will delete link to Project in middle_table.

class Project {

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

Documentation:
Doctrine association mapping
mysql foreign key contraint

How truncate a table in MySQL that is using ManyToMany field

2 Answers2