mysql - How to change the foreign key referential action? (behavior)

Question

I have set up a table that contains a column with a foreign key, set to ON DELETE CASCADE (delete child when parent is deleted)

What would the SQL command be to change this to ON DELETE RESTRICT? (can't delete parent if it has children)

Answer 1

_{Old question but adding answer so that one can get help}

Its two step process:

Suppose, a table1 has a foreign key with column name fk_table2_id, with constraint name fk_name and table2 is referred table with key t2 (something like below in my diagram).

   table1 [ fk_table2_id ] --> table2 [t2]

First step, DROP old CONSTRAINT: (reference)

ALTER TABLE `table1` 
DROP FOREIGN KEY `fk_name`;  

_{notice constraint is deleted, column is not deleted}

Second step, ADD new CONSTRAINT:

ALTER TABLE `table1`  
ADD CONSTRAINT `fk_name` 
    FOREIGN KEY (`fk_table2_id`) REFERENCES `table2` (`t2`) ON DELETE CASCADE;  

_{adding constraint, column is already there}

Example:

I have a UserDetails table refers to Users table:

mysql> SHOW CREATE TABLE UserDetails;
:
:
 `User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`)
:
:

First step:

mysql> ALTER TABLE `UserDetails` DROP FOREIGN KEY `FK_User_id`;
Query OK, 1 row affected (0.07 sec)  

Second step:

mysql> ALTER TABLE `UserDetails` ADD CONSTRAINT `FK_User_id` 
    -> FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`) ON DELETE CASCADE;
Query OK, 1 row affected (0.02 sec)  

result:

mysql> SHOW CREATE TABLE UserDetails;
:
:
`User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES 
                                       `Users` (`User_id`) ON DELETE CASCADE
: