How to insert a composite primary key into another table?

Question

I have a composite primary key that I would like to insert into another table.

create table courses_instructors
(
courseID int foreign key references Course(C_ID) not null,
instructorID int foreign key references Instructor(I_ID) not null,
primary key (courseID, instructorID), --coourseID and instructorID combined is the composite PK
courseTerm varchar(50) not null,
courseNumber int not null,
courseLocation varchar(50),
courseTime varchar(50),
courseMaxOccupancy int,
courseSeatAvailable int
)

create table courses_students
(
studentID int foreign key references student(S_ID) not null,
courseID int, -- foreign key -- I want this value to the be value that represents the composite PK from the courses_instructors
primary key(studentID, courseID), -- these 2 fields combined would make the composite PK, but with the courseID value I will be able to identify who is the instructor for a course and the other details from the course_instructor table
courseOutcome varchar(50)
)

All the course come from a course table which only contains the course name and the disciple along with a descrption. The course table has a primary key that identifies each course uniquely.

Answer 1

To refer composit primary key, Courses_Students table should be having both the columns CourseID and InstructorID.

And then

ALTER TABLE Courses_Students
ADD CONSTRAINT FK_Courses_Students
FOREIGN KEY(CourseID, InstructorID) REFERENCES Courses_Instructors(CourseID, InstructorID)

Or the table definitions should look like,

create table courses_instructors
(
courseID int foreign key references Course(C_ID) not null,
instructorID int foreign key references Instructor(I_ID) not null,
primary key (courseID, instructorID), --coourseID and instructorID combined is the composite PK
courseTerm varchar(50) not null,
courseNumber int not null,
courseLocation varchar(50),
courseTime varchar(50),
courseMaxOccupancy int,
courseSeatAvailable int
)

create table courses_students
(
studentID int foreign key references student(S_ID) not null,
courseID int,
instructorId int,
FOREIGN KEY(CourseID, InstructorID) REFERENCES Courses_Instructors(CourseID, InstructorID),
primary key(studentID, courseID, InstructorId),
courseOutcome varchar(50)
)

Answer 2

You can either refer two columns as already mentioned or add a surrogate key such as an identity column or GUID to the primary table and refer with to it - It usually performs better.

Answer 3

The course_instructors table is an intersection table implementing an m-m relationship between, as may be easily guessed, course entities and instructor entities. Almost invariably, I don't add a surrogate key to such a table for the simple reason that such a key would never be used. A typical user has a reference to one entity or the other and wishes to see all the other entities it relates to. Or sometimes the user has references to both entities and wished to get the details of their relationship.

This rule is not without exceptions, however, and your use case is just such an example. The table not just expresses a relationship between two entities but becomes an entity unto itself: a class offering. A student will select a class from a published schedule for the class and day/time desired. This will be identified by a class code number of some sort.

This code is what will be used to register for the desired class. In cases such as these, it makes sense to create a surrogate key for the intersection table -- which then becomes the class code printed in the catalog. Thus you would use this class code to refer to the relationship that defines the class offering and not use the composite key.

It looks like you already have such a composite key: the course_number field. You don't have it defined as unique but doesn't it uniquely identity the combination of course, instructor, location and time that makes up each class offering?