Implementing Many-to-Many Relationships with Foreign Keys : many to many « Table Join « SQL Server / T-SQL Tutorial






4> CREATE TABLE Students(
5>     StudentID int Primary Key,
6>     FirstName nvarchar(30),
7>     LastName nvarchar(50),
8>     FullName AS (FirstName + ' ' + LastName)
9> )
10> GO
1>
2> CREATE TABLE Classes(
3>     ClassID int Primary Key,
4>     ClassTitle varchar(50)
5> )
6> GO
1>
2> CREATE TABLE ClassGrades(
3>     ClassID int,
4>     StudentID int,
5>     GradeLetter varchar(2),
6>     Constraint PK_ClassGrades
7>         PRIMARY KEY(ClassID, StudentID),
8>     Constraint FK_Classes_ClassID
9>         FOREIGN KEY(ClassID)
10>         REFERENCES Classes(ClassID) ON UPDATE CASCADE,
11>     Constraint FK_Students_StudentID
12>         FOREIGN KEY(StudentID)
13>         REFERENCES Students(StudentID) ON UPDATE CASCADE
14> )
15> GO
1>
2> --Insert classes rows
3> INSERT Classes VALUES(1,'SQL')
4> INSERT Classes VALUES(999,'Java')
5> GO

(1 rows affected)

(1 rows affected)
1>
2> --Insert Students rows
3> INSERT Students VALUES(1, 'Poor', 'DBA')
4> INSERT Students VALUES(2, 'Better', 'DBA')
5> GO

(1 rows affected)

(1 rows affected)
1>
2> --Insert ClassGrades rows
3> INSERT ClassGrades VALUES(1, 1, 'C+')
4> INSERT ClassGrades VALUES(1, 2, 'A+')
5> INSERT ClassGrades VALUES(999, 2, 'A')
6> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> --Show table values after initial population
3> SELECT S.FullName, C.ClassTitle, CG.GradeLetter
4> FROM Classes C, ClassGrades CG, Students S
5> WHERE C.ClassID = CG.ClassID AND
6>     S.StudentID = CG.StudentID
7> GO
FullName                                                                          ClassTitle                                         GradeLetter
--------------------------------------------------------------------------------- -------------------------------------------------- -----------
Poor DBA                                                                          SQL                                                C+
Better DBA                                                                        SQL                                                A+
Better DBA                                                                        Java                                               A

(3 rows affected)
1>
2> drop table ClassGrades;
3> drop table Classes;
4> drop table Students;
5> GO








4.10.many to many
4.10.1.Update a many-to-many relation (student, class, classgrade)
4.10.2.Implementing Many-to-Many Relationships with Foreign Keys