Constraint check (Options)
1>
2> CREATE TABLE ClassGrades(
3> ClassID int,
4> StudentID int,
5> GradeLetter varchar(2),
6> Constraint PK_ClassGrades PRIMARY KEY(ClassID, StudentID),
7> Constraint CK_GradeRange_ClassID CHECK (LEFT(UPPER(GradeLetter),1) LIKE '[A-F]' AND ClassID < 1000)
8> )
9> GO
1>
2> INSERT ClassGrades VALUES(1, 1, 'C+')
3> GO
Msg 2627, Level 14, State 1, Server JAVA2S\SQLEXPRESS, Line 2
Violation of PRIMARY KEY constraint 'PK_ClassGrades'. Cannot insert duplicate key in object 'dbo.ClassGrades'.
The statement has been terminated.
1> INSERT ClassGrades VALUES(1, 2, 'A+')
2> GO
Msg 2627, Level 14, State 1, Server JAVA2S\SQLEXPRESS, Line 1
Violation of PRIMARY KEY constraint 'PK_ClassGrades'. Cannot insert duplicate key in object 'dbo.ClassGrades'.
The statement has been terminated.
1> INSERT ClassGrades VALUES(1, 3, 'V-')
2> GO
(1 rows affected)
1> INSERT ClassGrades VALUES(1001, 1, 'A')
2> GO
(1 rows affected)
1> INSERT ClassGrades VALUES(999, 2, 'A')
2> GO
(1 rows affected)
1> select * from ClassGrades
2> GO
ClassID StudentID GradeLetter
----------- ----------- -----------
1 1 A
1 2 B-
1 3 V-
999 2 A
1001 1 A
(5 rows affected)
1>
2> drop table ClassGrades
3> GO
1>
2>
Related examples in the same category