laravel - 外键约束: cannot drop table because other objects depend on it

Question

我正在 Laravel 5.1 上运行迁移，并将数据库从 Mysql 切换到 Postgres。

通常，我可以在运行 down 迁移之前将外键检查设置为 0，如下所示:

- DB::statement('SET FOREIGN_KEY_CHECKS = 0');
- Do stuff
- DB::statement('SET FOREIGN_KEY_CHECKS = 1');

Postgres 不提供此功能。

在运行迁移时，我收到错误:

Dependent objects still exist: 7 ERROR: cannot drop table table2 because other objects depend on it

DETAIL: constraint table1_table2_table1_id_foreign on table table1_table2 depends on table table2

HINT: Use DROP ... CASCADE to drop the dependent objects too. (SQL: drop table "table2")

问题:当我在外键创建上设置 ->onDelete('cascade'); 时，这个投诉对我来说很好奇。为什么会发生这种情况？

片段:

创建Table1表:

...
public function down()
{
    Schema::drop('table1_table2');
    Schema::drop('table1'); 
}

创建Table2表(表1迁移后调用):

...
public function down()
{
    Schema::drop('table2'); 
}

创建外键表(调用最后一次迁移)

public function up()
{
  Schema::table('table1_table2', function(Blueprint $table)
  {
    $table->foreign('table1_id')->references('id')->on('table1')->onDelete('cascade');
    $table->foreign('table2_id')->references('id')->on('table2')->onDelete('cascade');
  });
  ...
 }

public function down()
{
    ...
    Schema::table('table1_table2', function(Blueprint $table)
    {
        $table->dropForeign('table1_id');
        $table->dropForeign('table2_id');
    });
    ...
}

Answer 1

This complaint is curious to me as I set ->onDelete('cascade'); on the foreign key creations. Why is this happening?

这里的关键术语是“删除时” - 当您从一个表中删除一行时，该选项将确定具有引用该行的外键的行是否将被删除也删除了。

但是，您的更改脚本并没有删除行，而是删除表。因此，这是一个不同的事件，不受外键上的 ON DELETE 选项的影响。

提示中提到的CASCADE是DROP TABLE语句中的关键字，discussed in the manual under "Dependency Tracking" :

关键引述:

When you create complex database structures involving many tables with foreign key constraints, views, triggers, functions, etc. you implicitly create a net of dependencies between the objects. For instance, a table with a foreign key constraint depends on the table it references.

和:

if you do not want to bother deleting all the dependent objects individually, you can run DROP TABLE products CASCADE; and all the dependent objects will be removed, as will any objects that depend on them, recursively. In this case, it doesn't remove the orders table, it only removes the foreign key constraint.

和:

Almost all DROP commands in PostgreSQL support specifying CASCADE.