How can I add a "ON DELETE CASCADE"

Question

I already have my forein keys and so on. I only need to add the ON DELETE CASCADE Option. How can I do that? Something with Alter Table? I'm using mysql with phpmyadmin

Answer 1

I don't know which version of phpMyAdmin you're using, but on the 4.5.2 version, here's how it works if you want to do it without manually writing the SQL query :

1. Select your table
2. Go to Structure tab
3. Go to Relation view "sub-tab"
4. Define your constraints
5. Save your changes

Note that this is not possible with the MyISAM storage engine (seems to be the default one on my installation) and you have to select the InnoDB one :

1. Select your table
2. Go to Operations tab
3. Change Storage Engine to InnoDB
4. Save

For the older phpMyAdmin versions, I know that it was slightly different, as the Relation view was accessible by a link below the table structure.

Answer 2

The simplest way would be to delete the current constraint and add new one with ON DELETE

To delete: ALTER TABLE mytable DROP FOREIGN KEY 'fk_anothertable'

Then create it again:

   ALTER TABLE mytable
   ADD CONSTRAINT 'fk_anothertable'
   FOREIGN KEY ('aColumn' )
   REFERENCES 'anotherTable' ('aColumn' )
   ON DELETE CASCADE

Answer 3

Unfortunately, you can only create and drop constraints using alter table, you cannot change an existing one. There is no separate alter constraint command either. Therefore all you can do is drop the foreign key and recreate it adding the on delete cascade clause.