SQL约束讲解

SQL约束讲解

编辑人：星辰·樱

一．约束的类型

SQL Server 系统提供了6种约束类型，即not null非空约束，primary key主键约束，Foreign key外键约束，unique唯一约束，check检查约束和default 默认约束。

约束与完整性之间的关系

域完整性：default ，check。实体完整性：primary key ，unique。

参照完整性：foreign key。

二．定义约束的方式

定义约束可以使用create table语句或alter table语句完成。前者表示创建表的同时定义约束，后者表示在已有表中定义约束。

定义约束时，即可以所约束放在一个列上，也可以约束放在多个列上。前者称为列级约束，后者称为表级约束。

定义约束和修改约束时，要考虑以下几个因素：

1.不必删除表，就可以直接创建、修改表和删除约束的定义。

2.应该在应用程序中增加错误检查机制，测试数据是否与约束相冲突。

3.当在表上增加约束时，SQL Server系统将检查表中的数据是否与约束冲突。

4.一般情况，约束的名称应该符合，约束类型简名_表名_列名_代号这样的形式。

1.主键约束

主键约束用来强制数据的实体完整性，它是在表中定义一个主建来唯一标识表中的每行记录。

－－T－SQL语句

Create table 表名

(列名数据类型constraint 主键约束名primary key [,···n] ---列级主键Constraint 主键约束名primary key (列名[,···n]) ---表级主键——alter table命令

Alter table 表名

Add constraint 列名primary key [clustered｜nonclustered] {(列名[,···n])} ·clustered：表示在该列上建立聚集索引。

·nonclustered：表示在该列上建立非聚焦索引。

2.NUIQUE约束

Unique约束主要用来限制表的非主键列中允许输入重复值，用来强调数据的

实体完整性。

——在创建表的时候创建惟一约束：

Create table 表名

(列名数据类型constraint 惟一约束名unique

[,···n]

[,constraint 惟一约束名unique(列名[,···n])])

——为已存在的表创建惟一约束：

Alter table 表名

Add constraint 列名unique [clustered｜nonclustered] {(列名)[，···n]}

注意：主键与unique约束的区别主要如下

1.unique约束，主要用在非主键的一列或多列上要求数据惟一的情况。

2.Unique约束，允许该列上存在null值，而主键决不允许出现这种情况。

3.可以在一个表上设置多个unique约束，而在一个表中只能设置一个主键约束。

3.外键约束

外键是指一个表中的一列或列组合，它虽不是该表的主键，但却是另一个表的主键。

为已经存在的表创建惟一约束：

Alter table 表名

Add constraint 外键的约束名称foreign key {(列名)[，···]}

references 主键表名称[(主键表的主键列的名称[，···])]

4.DEFAULI约束

默认约束用来强制数据的域完整性，当设置列没有为该列提供输入值，则系统会自动将默认值赋给该列。

为已经存在的表创建惟一约束：

Alter table 表名

Add constraint 默认约束名称default 默认值[for 列名]

注意：（1）定义的常量值必须与该列的数据类型和精度是一致的。

（2）default约束只能应用于insert语句。

（3）每个列只能定义一个default约束。

（4）default约束不能放在有identity属性的列上或者数据类型为timestamp 的列上，因为这些列系统都会自动提供数据。

（5）default约束允许指定一些由系统函数提供的值。

5.CHECK约束

Check约束即检查约束，用来强制数据域的完整性，它使用逻辑表达式来限

制表中的列可以接受哪些数据值。

Alter table 表名

Add constraint check约束名称check (check约束的条件表达式)[，···n]

注意：

（1）一个列上可以定义多个CEHCK约束。

（2）当执行INSERT语句或UPDATE语句时，该约束验证相应的数据是否满足CHECK约束的条件，但是执行DELETE语句时不检查CEHCK约束。

（3）每个列只能定义一个DEFAULE约束。

（4）CHECK约束不能放在有IDENTITY属性的列上或者数据类型为timestamp 的列上，因为这些列系统都会自动提供数据。

（5）CHECK约束不能包含子查询语句。

三．约束管理

1.查看约束

[exec] sp_help 约束名称

如果约束存在文本信息，也可以用sp_helptext来查看

[exec] sp_helptext 约束名称

2.删除约束

Alter table 表名

Drop constraint 约束名称

四．默认值

默认约束与默认对象的不同：默认对象是独立于表中，需要先创建对象，然后将其绑定到一个列或多个列上，定义一次可以使用多次。

1.语法格式

Create default

Create default 默认值名称

As

常量表达式

2.需要注意的问题

（1）不能将create default语句与其他T-SQL语句放在同一个批处理中。

（2）只能在当前数据库中创建默认值对象，且名称必须惟一。

（3）默认值必须与其要绑定有列的数据类型兼容否则生成错误信息。

（4）如果默认值对于它所绑定的列而言太长，由该值会被截断。

（5）如果要绑定的列同时有相关联的规则，默认值不能违反相应的规则，如果违反同一列的规则，则也会出现错误信息。

3.使用默认值

Exec sp_bindefault ‘默认值名称’，‘表名.字段名’

注意：（1）默认值不能绑定到text、ntext、image、varchar(max)、

nvarchar(max)、varbinary(max)、xml和timestamp，也不能绑定到具有identity属性的列、计算列或已经具有default约束的列。

（2）可以使用sp_bindefault在不取消绑定现有默认值的情况下，将新的默认值绑定到指定列，覆盖原有的默认值。

（3）不能将默认值绑定到SQL Server2005的系统数据类型。

4.解除默认值

Exec sp_unbindefault ‘表名。字段名’

5.删除默认值

Drop default 默认值对象

五．规则

规则：在向表中的某列插入或更新数据时，用它来限制输入值的取值范围。与check检查约束不同的是，规则对象是独立于表的，需要先创建对象，然后将其绑定到一个列或多个列上，定义一次可以使用多次。

区别：（1）规则是一种数据库对象，它的作用与check约束相同，用来限制输入值的取值范围，实现强制数域的完整性。

（2）规则与check约束相比较，规则比check约束更复杂，规则需要单独创建，然后绑定到列上，而check可以在建表时由check table 语句将其作为表的一部分进行指定。

（3）在一个列上只能应用一个规则，但是却可以应用多个check约束。

（4）一个规则只需定义一次就可以被多次应用，可以应用于多个表或多个列，还可以应用到用户定义的数据类型上。而check则不能。

1.语法格式

Create rule 规则名称

As 定义规则的条件（第一个局部变量的前面都有一个@符号）

2.需要注意到的问题

（1）不能将create rule 语句与其他T－SQL语句放在同一个批处理中。

（2）只能在当前数据库中创建规则对象，且名称必须惟一。

（3）规则必须与其要绑定列的数据类型兼容，否则会生成错误信息。

3.绑定规则

Exec sp_bindrule ‘规则名称’，‘表名。字段名’

4.解除规则

Exec sp_unbindrule ‘表名。字段名’

5.删除规则

Drop rule 规则名称