SQL Server 学习笔记之约束

约束

关系型数据库的三大完整性

• 实体完整性：指实体属性中的标识属性不能为空、不能重复，该约束通过指定的主键实现，其约束由系统强制实施

• 参照完整性：

  实体中的外键可以为空，但是不能为错，举例：比如学员管理系统中，班级没有确定，学员就来报到，那就只好暂时不分班级，却不能随意写一个

  不能删除有外键约束的属性，比如，贷款表中贷款人属性对应的表

• 用户定义完整性：设计数据库的时候用户定义了某一行不能为空，该约束通过在指定列添加default、check、unique等方式实施

主键约束

​主键PK，用于强制表的实体完整性。在创建或修改表时，可以通过定义PRIMARY KEY约束来创建主键。

​创建PRIMARY KEY约束的方式有两种：

1. 直接对字段加PRIMARY KEY关键字
2. 通过额外加约束的方式

注意：

1. 不能对TEXT/IMAGE类型加PRIMARY KEY属性。事实上包括char(max)、nchar(max)、varchar(max)、nvarchar(max)和其他二进制等对象类型都不适合作主键；
2. 和其他对象一样，在整个数据库中，约束的名称不能重复，一般不为约束指定命名的话，系统会自动生成；
3. 指定联合主键（同一个名字的两个同学可能在不同班级，所以如果只用名字做主键是不够的，还需要班级作为另一个主键，组成联合主键,非特殊情况，不建议联合主键）的列最多是16个；
4. 单列指定PRIMARY KEY关键字后，该列数据不能为空，不能重复。
5. 删除有约束的列之前一定要先删除依赖于这个列的主键

​代码示例，1、增加主键约束的5种方法2、删除主键约束3、联合主键

外键（FOREIGN KEY）约束

设置外键关联的时候，添加级联选项，语法：

--级联删除定义   [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]--级联更新定义   [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

如果没有指定ON DELETE或ON UPDATE，则默认为NO ACTION ON DELETE ACTION 指定如果试图删除某一行，而该行的键被其它表的现有行中的外键所引用，则产生错误并回滚DELETE语句 ON UPDATE ACTION 指定如果试图更新某一行中的键值，而该行的键被其它表的现有行中的外键所引用，则产生错误并回滚UPDATE语句 ON DELETE CASCADE(向下传递) 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则也将删除所有包含那些外键的行 ON UPDATE CASCADE 指定如果试图更新某一行中的键值，而该行的键被其它表的现有行中的外键所引用，则组成外键的所有值也将更新到为该键指定的新值 timestamp(时间戳) 如果timestamp列是外键或被引用键的一部分，则不能指定CASCADE ON DELETE SET NULL 指定如果试图删除某一行，而该行的键被其它表的现有行中的外键所引用，则组成被引用行中的外键的所有值被设置为NULL ON UPDATE SET NULL 指定如果试图更新某一行中的键值，而该行的键被其它表的现有行中的外键所引用，则组成被引用行中的外键的所有值被设置为NULL ON DELETE SET DEFAULT 指定如果试图删除某一行，而该行的键被其它表的现有行中的外键所引用，则组成被引用行中的外键的所有值被设置为它们的默认值。为执行此约束，目标表中所有外键列必须具有默认定义 ON UPDATE SET DEFAULT 指定如果试图更新某一行中的键值，而该行的键被其它表的现有行中的外键所引用，则组成被引用行中的外键的所有值被设置为它们的默认值

not null约束

设计表的时候还不能确定的话，就先让其允许为空

DEFAULT约束

设计表的时候还不能确定的话，就先让其为默认值

注意：

• 定义的常量必须与该列的数据类型、精度等匹配
• 每个列只能定义一个DEFAULT约束
• DEFAULT约束只能应用于INSERT语句
• DEFAULT约束不能和IDENTITY属性列重复定义在一个列上
• DEFAULT约束允许的系统函数包括SYSTEM_USER/GETDATE和CURRENT_USER

--例子：not null约束和DEFAULT约束CREATE TABLE USERS (    U_ID int PRIMARY KEY IDENTITY(1,1),    U_NAME nvarchar(30) not null,             --not null约束    U_PASSWORD nvarchar(30) DEFAULT('123456'),--DEFAULT约束    U_REGDATE DATETIME DEFAULT(GETDATE())     --DEFAULT约束,设置注册时间)

现有表中添加约束

CREATE TABLE USERS (    U_ID int PRIMARY KEY IDENTITY(1,1),    U_SECONDNAME nvarchar(30) not null,             --not null约束    U_FIRSTNAME nvarchar(30) UNIQUE,--UNIQUE约束    U_SEX nchar(1) CHECK(U_SEX='男' or U_SEX ='女'),--CHECK约束    U_AGE tinyint CHECK(U_AGE>8 and U_AGE<80),--CHECK约束    U_PASSWORD nvarchar(30) DEFAULT('123456'),--DEFAULT约束    U_REGDATE DATETIME DEFAULT(GETDATE())     --DEFAULT约束,设置注册时间)   GOALTER TABLE USERSADD CONSTRAINT default_name DEFAULT 'U_NAME的默认' FOR U_NAME

CHECK约束

通过指定的逻辑表达式来限制列的取值范围

• 一个列上可以定义多个CHECK约束（每个列只能定义一个DEFAULT约束）
• CHECK在执行INSERT和UPDATE语句中被执行
• CHECK不能同IDENTITY属性列同时在一个列上使用
• CHECK不能包含子查询语句

UNIQUE约束

UNIQUE约束指定表中的某一列或多个列不能有相同的两行或两行以上的数据存在。一个表只能有一个PRIMARY KEY约束，万一表中还有其它的字段也需要有值不能重复的需求就只有选择使用UNIQUE约束

UNIQUE约束 主键约束 一个表可以定义多个UNIQUE约束 一个表只能定义一个主键（PK） 允许NULL值 不允许有NULL值

FOREIGN KEY约束可以引用UNIQUE约束

禁止与删除约束

添加约束的时候如果表中有数据的话，它会先检测这些已经存在的数据是否满足约束条件，如果不满足的话就不能添加约束，

解决方式：禁止对已有的数据进行检测

• 只能禁止CHECK约束和外键约束应用到表上，其他如唯一性和主键等则不行
• 已经存在的数据如果以后都不会再改变，这里禁用是可以的，但是以后这些数据如果要使用UPDATE修改的话还是必须满足约束

• 如果禁止检测已有的数据，在添加约束之前先使用WITH NOCHECK

  临时禁止表上的约束生效(关闭后必须开启)

• 关闭约束检测

  NOCHECK CONSTRAINT 约束名

  NOCHECK CONSTRAINT ALL

• 开启约束检测

  CHECK CONSTRAINT 约束名

  CHECK CONSTRAINT ALL

删除约束：DROP CONSTRAINT 约束名1,约束名2

​