这些外键会做什么？ [英] What will these foreign keys do?

问题描述

我使用了一个在线SQL生成器来帮助设计我正在处理的一些MySQL表。外键总是困惑我。

我想出的代码尝试添加这4个外键，但是我想在添加它们之前确保我想要它们。

$ b $ pre $ ALTER TABLE`users`添加外键（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`添加外键（thread_id）参考`mail_threads`（`thread_id`）;


所以基本上会限制我做什么？所有这些表中的4个，我需要能够单独更新，而不与他们搞乱，所以我不应该使用外键上它们？

解决方法

外键是数据库将关联分配给父表的一种方式。

我将从你的名单和工作我的方式。

底部FK指出列 mail_message.thread_id 是 mail_threads.thread_id
下一个FK指出列 mail_threads.folder_id 是对 mail_folders.folder_id

这意味着mail_thread属于mail_folder，而mail_message属于a mail_thread。所以如果你没有thread_id为20的mail_thread，你不能创建一个thread_id为20的mail_message，因为它没有任何意义（并且引用不起作用）。如果线程中有消息而没有级联删除（强制删除与约束关联的所有内容）或者先删除消息，那么它还会阻止你删除thread_id 20的mail_thread。

简而言之，这两个约束条件是：

mail_messages属于属于mail_folders的mail_threads

- 或 -

mail_folders具有零到许多mail_threads有零到许多mail_messages

SO问题与FK的好处/陷阱：

外键有什么问题？

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?

解决方案

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:
What's wrong with foreign keys?

这篇关于这些外键会做什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！