SQL Server/T-SQL Tutorial/Table Join/One to many

An one-to-many relation

3> CREATE TABLE Classes(
4>     ClassID int PRIMARY KEY,
5>     ClassTitle varchar(50)
6> )
7> GO
1>
2> CREATE TABLE ClassGrades(
3>     ClassID int REFERENCES Classes(ClassID)
4>         ON UPDATE CASCADE,
5>     StudentID int,
6>     GradeLetter varchar(2),
7>     Constraint PK_ClassGrades
8>         PRIMARY KEY(ClassID, StudentID)
9> )
10>
11> INSERT Classes VALUES(1,"SQL")
12> INSERT Classes VALUES(999,"Java")
13> GO
(1 rows affected)
(1 rows affected)
1>
2> INSERT ClassGrades VALUES(1, 1, "C+")
3>
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>
3> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter
4> FROM Classes C, ClassGrades CG
5> WHERE C.ClassID = CG.ClassID
6> GO
StudentID   ClassTitle                                         GradeLetter
----------- -------------------------------------------------- -----------
          1 SQL                                                C+
          2 SQL                                                A+
          2 Java                                               A
(3 rows affected)
1>
2> UPDATE Classes
3> SET ClassTitle = "The Life of Jesus Christ"
4> WHERE ClassID = 999
5> GO
(1 rows affected)
1>
2> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter
3> FROM Classes C, ClassGrades CG
4> WHERE C.ClassID = CG.ClassID
5> GO
StudentID   ClassTitle                                         GradeLetter
----------- -------------------------------------------------- -----------
          1 SQL                                                C+
          2 SQL                                                A+
          2 The Life of Jesus Christ                           A
(3 rows affected)
1>
2>
3> drop table ClassGrades;
4> GO
1>
2> drop table Classes;
3> GO

Enables the changes in the Classes table to propagate to the ClassGrades table

10> CREATE TABLE Classes(
11>     ClassID int PRIMARY KEY,
12>     ClassTitle varchar(50)
13> )
14> GO
1>
2> CREATE TABLE ClassGrades(
3>     ClassID int REFERENCES Classes(ClassID)
4>         ON UPDATE CASCADE,
5>     StudentID int,
6>     GradeLetter varchar(2),
7>     Constraint PK_ClassGrades
8>         PRIMARY KEY(ClassID, StudentID)
9> )
10>
11> INSERT Classes VALUES(1,"SQL")
12> INSERT Classes VALUES(999,"Java")
13> GO
(1 rows affected)
(1 rows affected)
1>
2> INSERT ClassGrades VALUES(1, 1, "C+")
3>
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> SELECT * FROM Classes
3> GO
ClassID     ClassTitle
----------- --------------------------------------------------
          1 SQL
        999 Java
(2 rows affected)
1>
2> UPDATE Classes
3> SET ClassID = 998
4> WHERE ClassID = 999
5> GO
(1 rows affected)
1>
2> SELECT * FROM Classes
3> GO
ClassID     ClassTitle
----------- --------------------------------------------------
          1 SQL
        998 Java
(2 rows affected)
1>
2> SELECT CG.StudentID, C.ClassTitle, CG.GradeLetter
3> FROM Classes C, ClassGrades CG
4> WHERE C.ClassID = CG.ClassID
5> GO
StudentID   ClassTitle                                         GradeLetter
----------- -------------------------------------------------- -----------
          1 SQL                                                C+
          2 SQL                                                A+
          2 Java                                               A
(3 rows affected)
1>
2> drop table ClassGrades;
3> GO
1>
2> drop table Classes;
3> GO