Laravel quicktip: Resolving “Cannot truncate a table referenced in a foreign key constraint”

Here is a quick little tip to prevent/resolve a foreign key error you may get in a relational database when trying to seed your database.

The error you may be getting looks like this:

  [Exception]                                                                  
  SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`example`.`companies`, CONSTRAINT  `companies_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES crm`.`statuses` (`id`)) (SQL: truncate `statuses`) (Bindings: array ( ))                                                                           

  [PDOException]                                                               
  SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`example`.`companies`, CONSTRAINT  `companies_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `crm`.`statuses` (`id`))

db:seed [--class[="..."]] [--database[="..."]]

Nasty little error, right? Luckily the solution is quite simple. In your Database Seed file, replace the following line

DB::table('yourtable')->truncate();

with this line:

DB::table('yourtable')->delete();

Hope that helps and makes your life a bit easier!

Share the knowledge!
002800