0

I have a table "student" table and "evaluation" table that refers to "student", when i tried DELETE any data, it's ok, but when i try to truncate it, it failed with message:

 ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`testdb`.`evaluation`, CONSTRAINT `fk_relationship_18` FOREIGN KEY (`stu_id`) REFERENCES `testdb`.`student` (`id`))

it shows the same error even when student table is empty.. any reason why this happens? or i should google how to reset the auto increment manually.

Kokizzu
  • 24,974
  • 37
  • 137
  • 233

1 Answers1

2

You cannot TRUNCATE the table that have FK constraint to it (just because truncate is not the same as delete).

To resolve this issue:

  1. Remove constraints.
  2. Perform TRUNCATE.
  3. Delete manually the rows that now have references to "nowhere".
  4. Create constraints.