Imagine I have three tables called profiles
, profiles_skills
and skills
. It's a HABTM relationship, where a profile can have many skills and a skill can belong to many profiles.
假设我有三个表,叫做profiles_skills和skills。这是一种HABTM关系,在这种关系中,概要文件可以具有许多技能,而一项技能可以属于许多概要文件。
I need to set up a foreign key constraint between these tables. My question is, what direction should the foreign key be? For example, do I do this:
我需要在这些表之间设置一个外键约束。我的问题是,外钥应该朝哪个方向?例如,我是否这样做:
ALTER TABLE profiles_skills ADD FOREIGN KEY (skill_id) REFERENCES skills(id);
ALTER TABLE profiles_skills ADD FOREIGN KEY (profile_id) REFERENCES profiles(id);
Or do I do this:
或者我这样做:
ALTER TABLE profiles ADD FOREIGN KEY (id) REFERENCES profiles_skills(profile_id);
ALTER TABLE skills ADD FOREIGN KEY (id) REFERENCES profiles_skills(skill_id);
I've never really followed a convention, I just do it the way I type it and haven't had a problem. But I've always wondered in the back of my mind if it even really matters.
我从来没有遵循过惯例,我只是按照我输入的方式去做,没有问题。但我一直在想,这是否真的重要。
1 个解决方案
#1
9
My question is, what direction should the foreign key be?
我的问题是,外钥应该朝哪个方向?
Foreign keys should be in the junction table (profiles_skills
), referencing the endpoint tables (profiles
and skills
).
外键应该位于连接表(profiles_skills)中,引用端点表(概要和技能)。
If you try to do it the other way around, you'll be able to "connect" non-existent profile and/or skill, which is exactly what foreign keys are supposed to prevent. It would also make it impossible to have an unconnected profile (or skill).
如果你尝试用另一种方式去做,你将能够“连接”不存在的概要文件和/或技能,这正是外键应该防止的。它还将使不可能有一个不相连的概要(或技能)。
#1
9
My question is, what direction should the foreign key be?
我的问题是,外钥应该朝哪个方向?
Foreign keys should be in the junction table (profiles_skills
), referencing the endpoint tables (profiles
and skills
).
外键应该位于连接表(profiles_skills)中,引用端点表(概要和技能)。
If you try to do it the other way around, you'll be able to "connect" non-existent profile and/or skill, which is exactly what foreign keys are supposed to prevent. It would also make it impossible to have an unconnected profile (or skill).
如果你尝试用另一种方式去做,你将能够“连接”不存在的概要文件和/或技能,这正是外键应该防止的。它还将使不可能有一个不相连的概要(或技能)。