Let's say my database is for ecommerce store. Database holds records of users and orders. I have 2 tables 'users' and 'orders'. 'orders' table have userId column(foreign key) that reference to 'users' table id column. When I want to delete a user- it throws an error because the 'orders' table have a record referencing this user's id. So how should I handle this?

I found 3 ways to overcome this.

1. not use foreign keys
2. use 'ON DELETE CASCADE' so when i delete user from 'users' table it will delete related order records of that user automaticaly.(is it a good idea?)
3. delete all child records and then delete parent record.

What is the best way?

CodePudding user response：

For the simple question of how do you delete a record and it's references...

Foreign keys are critical for the integrity of your database. Without foreign keys you easily wind up with records that refer to objects which no longer exists. Don't remove them.

Manually deleting the referencing rows is error prone and will break when you change the schema.

on delete cascade allows you to have referential integrity, and also be able to delete records.

The comments got into the larger question of whether deleting user and order records are a good idea. There is not enough information in your question to know what is best. That would be another question.

However, even if you decide to use a status field to set users and orders as inactive (a timestamp, not a flag, because you'll want to know when the user was deactivated), you still want to set up the tables with on delete cascade so when you eventually do delete inactive records (for example, perhaps an annual cleanup, or for testing, or due to a mistake) the delete will work.