Mega Code Archive
Update a many-to-many relation (student, class, classgrade)
<![CDATA[
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> SELECT * FROM Classes
3> GO
ClassID ClassTitle
----------- --------------------------------------------------
(0 rows affected)
1>
2>
3> --Insert classes rows
4> INSERT Classes VALUES(1,'SQL')
5> INSERT Classes VALUES(999,'Java')
6> 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> UPDATE Classes
3> SET ClassID = 998,
4> ClassTitle = 'Java'
5> WHERE ClassID = 999
6> GO
(1 rows affected)
1>
2> SELECT * FROM Classes
3> GO
ClassID ClassTitle
----------- --------------------------------------------------
1 SQL
998 Java
(2 rows affected)
1>
2> drop table ClassGrades;
3> drop table Classes;
4> drop table Students;
5> GO
]]>