CODEWITHSUNDEEP
sql-serverdatabase
ShoeLace1291
ShoeLace1291

Reputation: 4698

How do I create a relationship between two tables where one already has a relationship with another table?

I am trying to create a Many to Many relationship with SQL Server Management studio between two tables called Courses and Students. This relationship uses a junction table called Enrollment. I started by creating a 1:M relationship between Students and Enrollment so that the studentId column in the Enrollment table points to the studentId column in the Student table. That worked fine. My problem is occurring while I create my 1:M relationship between Courses and Enrollment. The courseId column in the Enrollment table needs to point to the courseId column in the Courses table. The relationship dialogue comes up and the columns under the Primary Key Table(Enrollment are automatically populated with courseId and studentId. The Courses table doesn't have a studentId column, so I remove this from the columns under the primary key table. This is where I get the error message 'The columns in table Enrollment do not match an existing primary key or UNIQUE value. This method worked fine for creating the 1:M between Students and Enrollment. Why am I getting this error all of a sudden?

enter image description here enter image description here

Upvotes: 0

Views: 1540

Answers (2)

Jo G
Jo G

Reputation: 65

Your Courses primary key includes a SemesterID which isn't in the Enrollment table. I'd suggest the SemesterID should be in the Enrollments table rather than in the Courses table as a Student would be enrolled in a course in a particular Semester.

Upvotes: 0

FLICKER
FLICKER

Reputation: 6693

The reason is your Courses table has 2 field in it's primary key. On solution is you add a new field named semesterId in Enrollment table and use both semesterId and courseId when creating foreign key.

Upvotes: 0

Related Questions