Supporting Basic Referential Integrity with Foreign Keys : Foreign Key « Constraints « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Constraints
3. Foreign Key

4>
5> CREATE TABLE Classes(
6>     ClassID int PRIMARY KEY,
7>     ClassTitle varchar(50)
8> )
9> 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> INSERT ClassGrades VALUES(998, 1, 'B')
3> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__ClassGrad__Class__4F7D9B64". The conflict occurred in database "master", table "dbo.Classes", column 'ClassID'.
The statement has been terminated.
1>
2> drop table ClassGrades;
3> GO
1>
2> drop table Classes;
3> GO