Unable to create Foreign Key

Question

There are two tables:

• Table Course with columns CourseID (PK, int, not null) and DepartmentID (PK, FK, int, not null), and
• Table Class with columns ClassID (PK, int, not null) and CourseID (int, not null).

I try to make Class.CourseID FK to Course.CourseID but I get this message:

The columns in table 'Course' do not match an existing primary key or UNIQUE constraint.

What would I do?

Answer 1

The "structure" of FK must be the same as PK - two fields. With only CourseID as a FK you can't distinguish between records.

Example: Let c1, c2 be Course records, d1 a class record:

c1: {1, 1}
c2: {1, 2}

Now assume d1 record to be sth like {1, 1} -> you meant c1 or c2?

You should make ONLY CourseID PK or make a complex FK (CourseID, DepartmentID) or create a surrogate PK in Course table. Personally, I would go for 1st or 3rd solution

Answer 2

DepartmentID should not be part of the primary key of table Course. Or if you need it like that, then restrucutre the FK in the Class table so it will reference both (so the real primary key of table Course).