在添加外键约束时，哪个方向是最佳实践?

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

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).

如果你尝试用另一种方式去做，你将能够“连接”不存在的概要文件和/或技能，这正是外键应该防止的。它还将使不可能有一个不相连的概要(或技能)。