foreign key constraint: cannot drop table because other objects depend on it

Question

I'm running migrations on Laravel 5.1 and am switching DBs from Mysql to Postgres.

Typically I could set foreign key checks to 0 prior to running down migrations as such:

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

Postgres does not offer this.

In running down migrations, I get error:

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")

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

Snippets:

Create Table1 Table:

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

Create Table2 Table (Called after table 1 migration):

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

Create Foreign Keys Table (last migration to be called)

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

IMSoP IMSoP · Accepted Answer · 2018-05-14T21:47:41

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

The key term here is "on delete" - when you delete a row from one table, that option will determine whether rows with foreign keys referencing that row will be deleted as well.

However, your change script is not deleting rows, it is dropping the table. This is therefore a different event, not effected by the ON DELETE option on the foreign key.

The CASCADE mentioned in the hint is a keyword on the DROP TABLE statement, discussed in the manual under "Dependency Tracking":

Key quotes:

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.

and:

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.

and:

Almost all DROP commands in PostgreSQL support specifying CASCADE.

foreign key constraint: cannot drop table because other objects depend on it

2 Answers