Laravel Eloquent truncate - Foreign key constraint

Question

I am having some issues with deleting data using Laravel 5. I seem to be stuck on a 'foreign key constraint', but 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 data points are 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 returned the same result.

Is this normal behavior? Am I missing something?

Answer 1

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.

Like it was said above, for cross-database support Schema::disableForeignKeyConstraints() and Schema::enableForeignKeyConstraints() can be used to disable checks on foreign keys. The usage of Model::truncate() vs DB::table()->truncate() is just a matter of preference, although the former will always work even if table name of model will internally change, so I'd recommend it.