Laravel雄辩截断 - 外键约束 [英] Laravel Eloquent truncate - Foreign key constraint

问题描述

我在使用Laravel 5删除数据时遇到一些问题。我似乎困在一个外键约束，而我不明白为什么。

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.

在我当前的数据库模型中，我有一个datapoints表，它有一个外键的sensors表（datapoints.sensors_id - > sensor.id）。

In my current database model I have a datapoints table, which has a foreign key to the sensors table (datapoints.sensors_id -> sensor.id).

我尝试的代码：

Route::get('/truncateData', function() {
DB::table('datapoints')->truncate();
DB::table('sensors')->truncate();
return 'Done...';

}）;

结果：

SQLSTATE [42000]：语法错误或访问冲突：1701不能
截断在外键约束中
（ alerting 。 datapoints ，CONSTRAINT datapoints_sensor_id_foreign
FOREIGN KEY（ sensor_id ）REFERENCES 警报。传感器（ id ））
（SQL：truncate sensors ）

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...';

最后，我也试图在delete语句之间显式地添加'DB :: commit返回相同的结果。

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

推荐答案

不，这是你的数据库工作方式。您不能截断由某个其他表引用的表。您可以执行

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.

这篇关于Laravel雄辩截断 - 外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！