What will these foreign keys do?

Question

I used an online SQL builder to help design some MySQL tables I'm working on. Foreign keys always confuse me.

The code I came up with tries to add these 4 foreign keys but I want to make sure that I want them before I add them.

ALTER TABLE `users` ADD FOREIGN KEY (user_id) REFERENCES `settings` (`user_id`);
ALTER TABLE `logins` ADD FOREIGN KEY (user_id) REFERENCES `users` (`user_id`);
ALTER TABLE `mail_threads` ADD FOREIGN KEY (folder_id) REFERENCES `mail_folders` (`folder_id`);
ALTER TABLE `mail_message` ADD FOREIGN KEY (thread_id) REFERENCES `mail_threads` (`thread_id`);

So basically what will to limit me from doing? All 4 of these tables below, I need to be able to update individually without them messing with the other, so should I not use a foreign key on them?

Answer 1

Foreign keys simply keep object integrity in place. This code will not let you change a user's ID if there is no row in settings with the same user ID. It will not let you change a user ID in the logins table if there is no corresponding user. Etc.

What do you mean by "messing with" the tables? You are free to update the users table at any time without updating the settings table, as long as every user always has a corresponding settings row. Otherwise, the query will fail.

You should leave the code in unless you have an excellent reason to remove it. If the foreign key constraints ever get in your way, it is simply a notice that your code is doing something wrong.

Answer 2

Foreign key will guarantee that you will not make a reference to a record that does not exist. The constraints created by foreign keys are often considered fundamental to enforce data integrity in a database.

In fact I think your first foreign key may need to be tweaked as follows:

ALTER TABLE `settings` ADD FOREIGN KEY (user_id) REFERENCES `users` (`user_id`);

This will guarantee that any user_id in the table settings will be a valid user_id already present in the users table. In addition, it will not allow you to delete a row in the users table if there is still a row in the settings table that references the user that is going to deleted. Thus it will also make sure there will be no orphan rows.

Your original foreign key would have required the settings row to be inserted before the users row, which I believe is not your intent. That foreign key is checking for a user_id in the settings table before allowing a new user in the users table.

As for the following foreign key:

ALTER TABLE `logins` ADD FOREIGN KEY (user_id) REFERENCES `users` (`user_id`);

This will also guarantee that any user_id in the table logins will be a valid user_id already present in the users table. The database will not allow you to have a record with user_id = 100 in table logins if there is not a user with user_id = 100 in the table users.

You can apply the same logic to the mail_threads constraint, which requires a folder_id already present int the mail_folders table, and the same for the mail_message constraint which requires a valid thread_id.

Answer 3

Foreign Keys are a way for the database to assign an association to a 'parent' table.

I'll start from the bottom of your list and work my way up.

The bottom FK states that the column mail_message.thread_id is a reference to mail_threads.thread_id The next FK states that the column mail_threads.folder_id is a reference to mail_folders.folder_id

What this means is that a mail_thread "belongs to" a mail_folder, and a mail_message "belongs to" a mail_thread. So if you have no mail_thread with a thread_id of 20 you can not create a mail_message with a thread_id of 20, because it wouldn't make any sense (and the reference wouldn't function). It also prevents you from deleting a mail_thread of say thread_id 20 if there are messages within that thread without either cascading the delete (forcing the deletion of everything associated through the constraints) or deleting the messages first.

In a nutshell, those two constraints say:
mail_messages belong to mail_threads which belong to mail_folders
-or-
mail_folders have zero to many mail_threads which have zero to many mail_messages

SO Question with the benefits/pitfalls of FKs:
http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys