SQL Server/T-SQL/Constraints/Column Constraints

Add constraints for two columns

1> CREATE TABLE T (
2>     int1 int IDENTITY PRIMARY KEY,
3>     vch1 varchar(5) CHECK (LEN(vch1) > 0),
4>     vch2 varchar(5) CONSTRAINT CK_LEN_TOO_SHORT CHECK (LEN(vch2) > 0)
5> )
6> GO
1>
2> INSERT T (vch1, vch2) VALUES("a","b")
3> GO
(1 rows affected)
1>
2> INSERT T (vch1, vch2) VALUES("","b")
3> GO
Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 2
The INSERT statement conflicted with the CHECK constraint "CK__T__vch1__7F01C5FD". The conflict occurred in database "master", table "dbo.T", column "vch1".
The statement has been terminated.
1> INSERT T (vch1, vch2) VALUES("a","")
2> GO
Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_LEN_TOO_SHORT". The conflict occurred in database "master", table "dbo.T", column "vch2".
The statement has been terminated.
1> INSERT T DEFAULT VALUES
2> GO
(1 rows affected)
1>
2> select * from t
3> GO
int1        vch1  vch2
----------- ----- -----
          1 a     b
          4 NULL  NULL
(2 rows affected)
1> drop table t
2> GO
1>

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 sqle\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 sqle\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>

Constraint: PRIMARY KEY

1>
2> CREATE TABLE T (
3>     int1 int PRIMARY KEY,
4>     bit1 bit NOT NULL DEFAULT 0
5> )
6> GO
1> INSERT T (int1, bit1) VALUES (1, 1)
2> GO
(1 rows affected)
1> INSERT T (int1, bit1) VALUES (2, 0)
2> GO
(1 rows affected)
1> INSERT T (int1) VALUES (3)
2> GO
(1 rows affected)
1> INSERT T (bit1) VALUES (1)
2> GO
Msg 515, Level 16, State 2, Server sqle\SQLEXPRESS, Line 1
Cannot insert the value NULL into column "int1", table "master.dbo.T"; column does not allow nulls. INSERT fails.
The statement has been terminated.
1> INSERT T (int1, bit1) VALUES (3,1)
2> GO
Msg 2627, Level 14, State 1, Server sqle\SQLEXPRESS, Line 1
Violation of PRIMARY KEY constraint "PK__T__07970BFE". Cannot insert duplicate key in object "dbo.T".
The statement has been terminated.
1>
2> select * from t
3> GO
int1        bit1
----------- ----
          1    1
          2    0
          3    0
(3 rows affected)
1>
2> drop table t
3> GO
1>
2>

Data length: greater than 0

1> CREATE TABLE T (
2>     int1 int IDENTITY PRIMARY KEY,
3>     vch1 varchar(5) CHECK (LEN(vch1) > 0) NOT NULL,
4>     vch2 varchar(5) CONSTRAINT CK_LEN_TOO_SHORT CHECK (LEN(vch2) > 0) NOT NULL
5> )
6> GO
1> INSERT T (vch1, vch2) VALUES("a","b")
2> GO
(1 rows affected)
1> INSERT T (vch1, vch2) VALUES("","b")
2> GO
Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__T__vch1__02D256E1". The conflict occurred in database "master", table "dbo.T", column "vch1".
The statement has been terminated.
1> INSERT T (vch1, vch2) VALUES("a","")
2> GO
Msg 547, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_LEN_TOO_SHORT". The conflict occurred in database "master", table "dbo.T", column "vch2".
The statement has been terminated.
1> INSERT T DEFAULT VALUES
2> GO
Msg 515, Level 16, State 2, Server sqle\SQLEXPRESS, Line 1
Cannot insert the value NULL into column "vch1", table "master.dbo.T"; column does not allow nulls. INSERT fails.
The statement has been terminated.
1>
2> select * from t
3> GO
int1        vch1  vch2
----------- ----- -----
          1 a     b
(1 rows affected)
1>
2> drop table t
3> GO
1>
2>

Two constraints for one single column: Not NULL and default value

1> CREATE TABLE T (
2>     int1 int IDENTITY PRIMARY KEY,
3>     bit1 bit NOT NULL DEFAULT 0
4> )
5> GO
1> INSERT T (bit1) VALUES (1)
2> GO
(1 rows affected)
1> INSERT T (bit1) VALUES (0)
2> GO
(1 rows affected)
1> INSERT T DEFAULT VALUES
2> GO
(1 rows affected)
1> INSERT T (int1, bit1) VALUES (4,1)
2> GO
Msg 544, Level 16, State 1, Server sqle\SQLEXPRESS, Line 1
Cannot insert explicit value for identity column in table "T" when IDENTITY_INSERT is set to OFF.
1>
2> select * from t
3> GO
int1        bit1
----------- ----
          1    1
          2    0
          3    0
(3 rows affected)
1>
2> drop table t
3> GO
1>