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

by

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
:

More Related Contents:

  1. MySQL Creating tables with Foreign Keys giving errno: 150
  2. MySQL Error 1215: Cannot add foreign key constraint
  3. Mysql error 1452 – Cannot add or update a child row: a foreign key constraint fails
  4. MySQL Cannot Add Foreign Key Constraint
  5. How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?
  6. Required to join 2 tables with their FKs in a 3rd table
  7. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  8. Basics of Foreign Keys in MySQL?
  9. Can a foreign key reference a non-unique index?
  10. MySQL – Conditional Foreign Key Constraints
  11. MySql Error 150 – Foreign keys
  12. Add Foreign Key to existing table
  13. How to create relationships in MySQL
  14. How to truncate a foreign key constrained table?
  15. MySQL Removing Some Foreign keys
  16. Force InnoDB to recheck foreign keys on a table/tables?
  17. Is it possible to reference one column as multiple foreign keys?
  18. How to insert values in table with foreign key using MySQL?
  19. Show constraints on tables command
  20. Meaning of “n:m” and “1:n” in database design
  21. Proper database model for a user feedback system (an interesting case)
  22. MySQL Errno 150
  23. How to use django models with foreign keys in different DBs?
  24. How to remove constraints from my MySQL table?
  25. Foreign key constraints: When to use ON UPDATE and ON DELETE
  26. MySQL DROP all tables, ignoring foreign keys
  27. MySQL “ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)”
  28. MySQL errorno 121
  29. “General error: 1005 Can’t create table” Using Laravel Schema Build and Foreign Keys
  30. MySQL Relationships

Leave a Comment