CODEWITHSUNDEEP
mysql
mixkat
mixkat

Reputation: 3785

Delete multiple entries with foreign key on delete cascade

I am trying to delete a certain field from one table and I want it to delete all the associated fields (ones containing the appropriate fk) from a different table.

Table1

      id | name | fname

Table 2

      id | table1Id | ...

The table1Id is set as a foreign key referencing the primary key of Table1 and I have set ON DELETE CASCADE

Now my code is simple and it deletes the row from Table1 like so:

function deleteWill($Id)
    {
        $stmt = $this->db->stmt_init();
        if($stmt->prepare('DELETE FROM Table1 where id= ?'))
        {
            $stmt->bind_param("i", $Id);
            $stmt->execute();
            $stmt->close();
            return true;
        }
        else
        {
            $stmt->close();
            return false;
        }
    }

However the CASCADE option doesnt work!

Does it have to do with the fact that I have multiple rows in Table2 that have the same fk?

If so how can I fix this so that it deletes all the associated rows in Table2?

Upvotes: 1

Views: 211

Answers (1)

spencer7593
spencer7593

Reputation: 108380

No, multiple rows in Table2 should not be a problem.

There's not enough information to determine what is causing the behavior you observe. If your statement is completing successfully, and the expected row is being removed from Table1, but the matching rows in Table2 are not being deleted...

Then the first suspect is that your tables are using the MyISAM engine instead of the InnoDB engine. (MyISAM doesn't enforce foreign key constraints, but InnoDB does.) The output from: 

SHOW CREATE TABLE Table1
SHOW CREATE TABLE Table2

will reveal which engine is being used. I'd start there. There are some other possibilities, such as verifying that foreign_key_check = 1;

To change the engine for a table from MyISAM to InnoDB:

ALTER TABLE Table1 ENGINE = InnoDB ;

This will require a rebuild of the entire table; it basically needs to create a new InnoDB table and copy all of the data from the MyISAM table. So, don't do this on a large table while it is being used...

Upvotes: 1

Related Questions