help with deleting relational tables

Question

I have four tables:

• users: id, thread_id
• threads: id, language_id
• posts: id, user_id, language_id
• languages: id

USERS.thread_id is a foreign key to THREADS.id, POSTS.user_id is foreign key to USERS.id and POSTS.language_id foreign key to LANGUAGES.id.

I can't delete an user because the POSTS.user_id will throw a foreign key constraint error, and I can't delete the post because I want all the posts (and threads) to be readable there even if the user is deleted.

What should I do?

Answer 1

Looks like everything is working as designed :) If MySQL let you delete the user while leaving posts.user_id pointing to it, your DB would become inconsistent.

If you want posts from deleted users to be readable, reset their user_id to something else (0? NULL?) before deleting the user.

If you want the user info to remain too, then you obviously can't delete the user row. You should add some kind of 'user is deleted' column that changes how the user is shown on the UI.

Answer 2

Foreign keys are for enforcing data integrity. Since you have a reason to have posts and threads exist without a valid user id, then you don't really need the foreign key data integrity.

I would either remove the foreign key entirely, or utilize the ON DELETE portion of a foreign key creation clause. You can have MySQL CASCADE, RESTRICT, or SET NULL when a referenced foreign value changes.

In this case, you could create the foreign key with ON DELETE SET NULL, and the user id would be set to NULL in your posts table when you delete a user. Foreign keys are created with RESTRICT by default, which is why you can't delete the user and leave an orphaned value in the posts table.

Answer 3

This is known as a soft delete and you can see it at work right here on SO. When you see an answer from a 'deleted' user, they're grayed out.

Keep the user in the database but add a flag column isDeleted which you set when the user is deleted.

Then (obviously) disallow any logins for that user and (optionally) display them specially.

Answer 4

Don't actually add foreign key restraints to your tables. They aren't necessary. Without them you're free to do whatever you want. Only add them if they are necessary.