в 13:46, 26 мая 2010

2010-05-26T13:46:06Z

Admin: 1 версия

2010-05-26T10:21:23Z

1 версия

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

==Add constraints for two columns==



<source lang="sql">

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>

</source>

==Constraint check (Options)==



<source lang="sql">

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>

</source>

==Constraint: PRIMARY KEY==



<source lang="sql">

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>

</source>

==Data length: greater than 0==



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

</source>

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



<source lang="sql">

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>

</source>

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

SQL Server/T-SQL/Constraints/Column Constraints - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия