CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
James Dawson
James Dawson

Reputation: 5409

When adding a foreign key constraint, which direction is best practice?

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.

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.

Upvotes: 9

Views: 7451

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

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

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

Upvotes: 13

Related Questions