Search code examples
mysqlforeign-keysconstraintsalter-table

MySQL: How to modify a column to add property "On Delete Cascade"?

I'm trying to add on delete cascade to a foreign key. I've have tried the below but can not get it to work:

mysql> alter table visits modify fk_targets_visits on delete cascade;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on delete cascade' at line 1
mysql> alter table visits v modify v.fk_targets_visits on delete cascade; // same error

What is the proper syntax for this?

Solution

  • You can do like this : 

    ALTER TABLE TABLE_NAME
ADD CONSTRAINT YOUR_CONSTRAINT
FOREIGN KEY (YOUR_FK)
REFERENCES TABLE_NAME (YOUR_FK)
ON DELETE CASCADE;