Skip to content
Advertisement

How do I make a foreign key reference to a specific cell when the primary key is known in sql?

I’ve been searching and tackling this for hours and I’m sure there’s a super easy way to do this.

I have two tables:

And in a third table, I want to make sure that the “program” is the same for the student and the branch.

The problem I run in to is that this tries to get the whole column from program in Students. I only want the value for said student not the whole column.

To clarify:

Students:

Branches:

Here Tom should only be able to enter the StudentBranches table with B1 and B3, since he belongs to Prog1 that is a program for B1. And Bob can only enter to B2.

Advertisement

Answer

As I said in my comment, it’s hard to tell what you’re trying to do but the foreign keys you’ve tried to create definitely look fishy.
Everything should be easier to understand if you limit each table to represent 1 thing only, that is respectively:

  • A student (without the attached program since that would be 2 things for the same table).
  • A program/branch (whatever you call it)
  • The link from a student to a branch.

This results in:

  • To fetch the program a student as registered to, you have to JOIN with StudentBranches.
  • I have removed the PRIMARY KEY from StudentBranches because your previous comment about “get the whole column” sounded like a student should be able to register to more than 1 program. If a student can register to exactly 1 program, then you need to add it back.
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement