Laravel Eloquent truncate - Foreign key constraint
Solution 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.
Solution 2
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();
Solution 3
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();
Wesley
Updated on April 16, 2021Comments
-
Wesley about 3 years
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
, CONSTRAINTdatapoints_sensor_id_foreign
FOREIGN KEY (sensor_id
) REFERENCESalerting
.sensors
(id
)) (SQL: truncatesensors
)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?
-
Wesley about 9 yearsIndeed. 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.
-
MacroMan over 4 yearsThis only works with MySQL and derivatives. Use the answer by @JoeGalind for cross compatibility
-
Luis Alfredo Serrano Díaz over 4 yearsIts better Use Eloquent as JoeGalind said, so you can use any database manager, that only works for mysql.
-
Zeenath over 3 yearsI am also having the same issue, Am using Laravel 7. But this won't work for me. It throws an error as: Error Call to undefined method Illuminate\Database\MySqlConnection::schema(). How can I resolve this?
-
Binar Web over 3 years@Zeenath I have edited my answer. This should work.
-
Kolawole Emmanuel Izzy over 3 yearsThis 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 ... I hope it helps someone
-
DeltaTango about 3 yearsMight have to do \Schema::