Laravel Eloquent truncate - Foreign key constraint
constraints, laravel, laravel-5, php, postgresql
Solution
No, this is the way your database works. You can't truncate table that is referenced by some other table. You may do something like
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
DB::table('datapoints')->truncate();
DB::table('sensors')->truncate();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');
to disable foreign key checks, truncate tables and enable it again.
Problem
I am having some issues with deleting data using Laravel 5. I seem to be stuck on a 'foreign key constraint', while I don't see why. In my current database model I have a datapoints table, which has a foreign key to the sensors table (datapoints.sensors_id -> sensor.id). The code I am trying: ``` Route::get('/truncateData', function() { DB::table('datapoints')->truncate(); DB::table('sensors')->truncate(); return 'Done...'; }); ``` The result: SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`alerting`.`datapoints`, CONSTRAINT `datapoints_sensor_id_foreign` FOREIGN KEY (`sensor_id`) REFERENCES `alerting`.`sensors` (`id`)) (SQL: truncate `sensors`) I would understand this constraint if the order would be inverse (first deleting sensors), but when datapoints is empty, there should be no problem deleting sensors? I have also tried: ``` DB::table('datapoints')->delete(); DB::table('sensors')->delete(); return 'Done...'; ``` Lastly I also tried adding explicitly 'DB::commit()' between the delete statements, but all return the same result. Is this normal behaviour? Am I missing something?