How to delete rows in table when PK is referenced in another table

Before data insertion, updates, deletes etc, database management systems that support foreign keys (like MySQL) make sure you do not introduce any inconsistencies. So, you need to make sure you’re not leaving orphan records behind in child tables.

The issue only arises when parent tables (where you want to delete records from) have corresponding records in the child tables and you want to delete one or more such records in parent table.

There are multiple solutions and workarounds to this issue. However, I strongly suggest that you rethink your database design as well.

In addition to that, you must be careful when you are making such changes. With other standard best practices and precautions, I suggest taking full backups and testing thoroughly in a production-like test/lab/dev environment first.

Finally, here is a list of some of the workarounds and solutions:

  • This is the most obvious one ie. you can delete corresponding child records first and then delete records from parent table. This way your data should remain in a consistent state.
  • Disable foreign key checks (SET foreign_key_checks = OFF;) in MySQL then delete records. However, it will leave orphan records (ie. data inconsistency in database) and might cause issues in the future.
  • Use "referential actions" feature for foreign keys (in MySQL). See 13.1.17.3 Using FOREIGN KEY Constraints for example. This could help you automatically delete records from child tables.
  • You can also insert a dummy (catch-all) record in the parent table and point all child records to this dummy record's PK ID (in parent table). This way MySQL will not complain when you're deleting parent table's records because they won't have any linked records in child tables (as the child records will be pointing to the dummy record now).
  • I recommend rethinking about your schema design as well.

    Lets confirm everything worked as we planned or not:

    mysql> SHOW CREATE TABLE test;
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                                                                                                    |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | test  | CREATE TABLE `test` (
      `id` int NOT NULL,
      `t` int DEFAULT NULL,
      `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `setting` json NOT NULL DEFAULT (json_object()),
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
    +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)
    
    mysql> SELECT * FROM test;
    Empty set (0.00 sec)
    
    mysql> INSERT INTO test (id, name) VALUES (1, 'geeks');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> SELECT * FROM test;
    +----+------+-------+---------+
    | id | t    | name  | setting |
    +----+------+-------+---------+
    |  1 | NULL | geeks | {}      |
    +----+------+-------+---------+
    1 row in set (0.00 sec)
    

    Did this tutorial help a little? How about buy me a cup of coffee?

    Buy me a coffee at ko-fi.com

    Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.

    IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

Comments (write a comment):

I think anything besides your third solution (foreign key referential actions) indicates a design problem in the database.

On the other hand, referential actions also encompass other solutions presented here. It handles case #1 (CASCADE) and sorta case #4 (SET NULL).

In short, let the database do the work. It knows where things are better than you do. :-) Posted by: Jan S on May 09, 2016

@Jan: Agreed for the most part.

Schema redesign is definitely recommended in such cases.

Also, first option imo is a normal case ie. you delete child records then parent records.

With other options, we need to keep in mind that its not always possible to ALTER table schema, so in such cases we have to look for other options. Also, using referential actions w.o proper planning might cause issues in future (hence more manual options for one-time use etc are suitable).

Plus, setting a column to NULL will depend on the column (if it allows NULL values or not). There is more (perhaps a new article :) ). Posted by: Geeksww on May 10, 2016

leave a comment on tutorial leave a comment