CODEWITHSUNDEEP
laravelpostgresqllaravel-5.1
user3871
user3871

Reputation: 12718

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

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

Upvotes: 2

Views: 16615

Answers (2)

xuma
xuma

Reputation: 484

For my case; you can get this error with this scenario;

If your table seems rollbacked in migration table (ex: maybe forget drop function first time) but table still exists on database you can get this error. migrate:fresh command will fail with given error message for this scenario.

You can drop table manually or add a row to migration table with that migration name and everything will be working normally.

Upvotes: 0

IMSoP
IMSoP

Reputation: 97708

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.

Upvotes: 5

Related Questions