Composite Primary Key Constraint

0
votes

Table Definitions:

Student(StudentID, StudentName) PK StudentID

Faculty(FacultyID, FacultyName) PK FacultyID

Course(CourseID, CourseName) PK CourseID

Qualified(FacultyID, CourseID, DateQualified) PK FacultyID CourseID

Section(SectionNo,Semester, CourseID) PK SectionNo Semester CourseID

Registration(StudentID, SectionNo, Semester) PK StudentID SectionNo Semester

How can I link table Registration and Section? StudentID in Registration as a foreign key references from StudentID in student table But What can I do with SectionNo and Semester? I can add a constraint on a foreign key Section_Semester (SectionNo, Semester), but those two attributes are not unique in Section table. Add a constraint on primary key in Section table?

Thanks

2
sqldatabase-designrdbmscomposite-key
Those aren't table definitions. CREATE TABLE statements are table definitions. (You can edit your question, and paste CREATE TABLE statements.) It's not clear from your description whether you're trying to create a foreign key in "registration" that references "section", or you're trying to create a foreign key in "section" that references "registration". Which is it? – Mike Sherrill 'Cat Recall'
Section(SectionNo, ... deviates from the convention of the other tables. Either SectionNo is itself a Primary Key (surrogate or natural), or it is just associated information with a key of (Semester, CourseID), or it is part of a composite key. Depending on which of the 3 is correct, your foreign key in Registration to Section must be adjusted to align with the Section primary key. As it stands, you will need to add CourseID to Registration and add it in the foreign key to Section. – StuartLC
In addition to seconding Stuart's and Mike's comments, the tables are not described, we do not know what you are trying to achieve, or which is the parent and which is the child in the two tables that you want to "link", or what purpose that serves. If I take the title as the question, the answer is "yes, you can". Each SQL and Non-SQL platform has different syntax, so you will have to look it up. – PerformanceDBA
@PerformanceDBA: Welcome back! – Mike Sherrill 'Cat Recall'
@Mike. Thanks. I am surprised anyone remembers me from four years ago! – PerformanceDBA

2 Answers

0
votes

I don't see how you can have a complete registration tuple without CourseID. If the combination of SectionNo and Semester are not unique, then what has the student registered for? All of the specified sections for the semester? No, they register for a particular course (which meets at specified days at specified times at a specified location).

Of course, I have no idea what Section represents. But whatever it represents, it obviously doesn't have the granularity to narrow down exactly what the student has registered for. You have to include that.

0
votes

If I understand you correctly, you're saying that your Section table has a primary key of (sectionNo,semester,courseID), and that there can thus be multiple records with the same sectionNo and semester? And then you want to connect Registration, which does not have a courseID, to this table? To the best of my knowledge you can't use a "references" clause because it will not link to a unique identifier. But that doesn't stop you from writing queries that join the two tables.

Update

Of course there are many possible queries depending on what it is you want to know. If, say, you want to know all the classes that student #17 has ever registered for, you could write:

select section.sectionno, section.semester, section.courseid
from registration
join section on section.sectionno=registration.sectionno 
  and section.semester=registration.semester 
where studentid=17

If you want all the courses for a given Registration record:

select coursed
from registration
join section on section.sectionno=registration.sectionno
  and section.semester=registration.semester
where registration.sectionno=@section
  and registration.semester=@semester
  and registration.studentid=@student

Now, hmm, writing that query this indicates to me that a student registers for all the courses making up a section and semester. Is this true? If there are multiple courses in a section and semester, does the student sign up for all of them at one time, or does he pick individual ones? If he picks, then it would seem that Registration needs to specify which course.

I don't know what "section" means here so I don't know how it relates.