в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:25:23Z

1 версия

Новая страница

== Implementing Many-to-Many Relationships with Foreign Keys==



<source lang="sql">
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</source>

== Update a many-to-many relation (student, class, classgrade)==



<source lang="sql">
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</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Table Join/many to many - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия