41

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?

julianstark999
  • 3,450
  • 1
  • 27
  • 41
Wesley
  • 979
  • 3
  • 11
  • 18

3 Answers3

76

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.

Maksym
  • 3,276
  • 3
  • 22
  • 27
  • Indeed. I eventually deleted the rows instead of truncating, but this would work also. I was mistaking when I said that the problem also ocurred with deletes. – Wesley Mar 18 '15 at 16:41
  • 1
    This only works with MySQL and derivatives. Use the answer by @JoeGalind for cross compatibility – MacroMan Nov 21 '19 at 15:47
  • 1
    Its better Use Eloquent as JoeGalind said, so you can use any database manager, that only works for mysql. – Luis Alfredo Serrano Díaz Dec 16 '19 at 00:46
  • This is how i solved mine without disabling and enabled foreign key checks [https://stackoverflow.com/questions/31192207/laravel-5-1-migration-and-seeding-cannot-truncate-a-table-referenced-in-a-foreig/64202344#64202344](https://stackoverflow.com/questions/31192207/laravel-5-1-migration-and-seeding-cannot-truncate-a-table-referenced-in-a-foreig/64202344#64202344) ... I hope it helps someone – Kolawole Emmanuel Izzy Oct 05 '20 at 04:13
45

If you prefer to use Eloquent objects, Maksym's answer the "Eloquent" way

use Illuminate\Support\Facades\Schema;
use App\Models\Datapoint;
use App\Models\Sensor;


Schema::disableForeignKeyConstraints();
Datapoint::truncate();
Sensor::truncate();
Schema::enableForeignKeyConstraints();
JoeGalind
  • 3,545
  • 2
  • 29
  • 33
13

In Laravel 7 and 8, for compatibility across 4 databases (MySql, Postgres, SQLite and SqlServer) and no Eloquent, you can use:

Schema::disableForeignKeyConstraints();
DB::table('datapoints')->truncate();
DB::table('sensors')->truncate();
Schema::enableForeignKeyConstraints();
Binar Web
  • 867
  • 1
  • 11
  • 26