数据库的约束

一、创建、修改和删除约束

• 约束是SQL Server提供的自动强制数据完整性的一种方式，通过定义列的取值规则，是强制完整性的标准机制。使用约束优先于触发器、规则和默认值。

1、非空约束

• 列的为空性决定表中的行是否可为该列包含空值。空值（NULL）不同于0、空白或长度为零的字符串，NULL的意思是没有输入，出现NULL值通常是值未知或未定义。

1.1、创建非空约束

• 可在用CREATE TABLE创建表时，使用NOT NULL关键字指定非空约束，语法如下：

  [CONSTRAIN <约束名>]  NOT NULL
  

1.2、修改非空约束

ALTER TABLE table_name
alter column_name column_type null | not null
table_name：要修改非空约束的表名
column_name：要修改非空约束的列名
column_type：要修改非空约束的列类型
null | not null：修改为空或非空
例：
USE TEST1
ALTER TABLE Agent
alter column Age int not null

2、主键约束

• 通过定义PRIMARY KEY约束来创建主键

2.1、创建主键约束

（1）在创建表时创建主键约束

USE TEST1
CREATE TABLE [dbo].[Tb1](
[ID][int]CONSTRAINT PK_ID PRIMARY KEY,
[Name][char](50),
[Sex][char](2),
[Age][int]
)
-- CONSTRAINT PK_ID PRIMARY KEY为创建一个主键约束，PK_ID为用户自定义的主键约束名称，该名称需是合法的标识符。

（2）在现有表中创建主键约束

ALTER TABLE table_name
ADD 
CONSTRAINT constraint_name
PRIMARY KEY[CLUSTERED | NONCLUSTERED]
{(Columns[,...n])}
CONSTRAINT：创建约束的关键字
constraint_name：创建约束的名称
PRIMARY KEY：表示所创建的约束的类型为主键约束
CLUSTERED | NONCLUSTERED：是表示为 PRIMARY KEY或 UNIQUE约束创建集聚或非集聚索引的关键字。
例：
USE TEST1
ALTER TABLE Agent
ADD CONSTRAINT ZJ_ID PRIMARY KEY(ID)

2.2、修改主键约束

• 若要修改 PRIMARY KEY 约束，必须先删除现有的 PRIMARY KEY 约束，然后再用新定义重新创建该约束。

2.3、删除主键约束

• 语法如下：

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name[,...n]
  constraint_name:主键名
  例：
  USE TEST1
  ALTER TABLE Tb1
  DROP CONSTRAINT PK_ID
  

3、唯一约束

• 唯一约束 UNIQUE 用于强制实施列集中的值的唯一性。根据 UNIQUE 约束，表中的任何两行都不允许有相同的列值。另外，主键也强制实施唯一性，但主键不允许 NULL 作为一个唯一值。

3.1、在创建表时创建唯一约束

• 例子：

  USE TEST1
  CREATE TABLE[dbo].[Employee]( --创建表
  [ID][int]CONSTRAINT UQE_ID UNIQUE,  --设置唯一约束
  [Name][char](50),
  [Sex][char](2),
  [Age][int]
  )
  

3.2、在现有表中创建唯一约束

• 其语法如下：

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  UNIQUE[CLUSTERED | NONCLUSTERED]
  {{column[,...n]}}
  
  参数说明
  table_name:要创建唯一约束的表名称
  constraint_name:唯一约束名称
  column:要创建唯一约束的列名称。
  例子：
  USE TEST1
  ALTER TABLE Agent
  ADD CONSTRAINT Unique1_ID  --设置唯一约束
  UNIQUE(ID)
  

3.3、修改唯一约束

• 若要修改唯一约束，必须首先删除现有的 UNIQUE 约束，然后用新定义重新创建。

3.4、删除唯一约束

• 语法如下：

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name[,..n]
  
  --例子
  USE TEST1
  ALTER TABLE Tb1
  DROP CONSTRAINT Unique_ID
  

4、检查约束

• 检查约束 CHECK 可以强制域的完整性；CHECK 约束类似于 FOREIGN KEY 约束，可以控制放入列中的值；但是，他们在确定有效值的方式上有所不同：FOREIGN KEY 约束从其他表获得有效值列表，而 CHECK 约束通过不急于其他列中的数据的逻辑表达式确定有效值。

4.1、创建检查约束

（1）在创建表时创建检查约束

• 例子：

  USE TEST
  CREATE TABLE [dbo].[Employee1](
  [ID][int]CONSTRAINT Unique2_ID UNIQUE,
  [Name][char](50),
  [Sex][char](2)CONSTRAINT CK_Sex Check(sex in('男','女')),
  [Age][int]
  )
  

（2）在现有表中创建检查约束

• 语法如下：

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  CHECK(logical_expression)
  
  参数说明
  table_name：要创建检查约束的表名称
  constraint_name：检查约束名称
  logical_expression：要检查约束的条件表达式
  
  例子：
  USE TEST
  ALTER TABLE [Tb_student]
  --ADD CONSTRAINT Check_Sex Check(性别 in('男','女'))
  ADD CONSTRAINT CK_tz Check(统招否 in(1,0))
  

检查约束.PNG

4.2、修改检查约束

• 修改表中某列的 CHECK 约束使用的表达式，必须先删除现有的 CHECK 约束，然后使用新定义重新创建，才能修改 CHECK 约束。

4.3、删除检查约束

• 删除检查约束的语法如下：

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name[,...n]
  
  --例子
  USE TEST
  ALTER TABLE Employee1
  DROP CONSTRAINT CK_Sex
  

5、默认约束

• 在创建或修改表时可通过默认约束 DEFAULT 来创建默认值。默认值可以是计算结果为常量的任何值，设置了默认值后将会为所在列的每一行分配一个默认值。

5.1、创建默认约束

（1）在创建表时创建默认约束

• 语法如下：

  USE TEST
  CREATE TABLE[dbo].[temp](   --创建表
  [ID][int],
  [Name][char](50),
  [Sex][char](2)CONSTRAINT Default_Sex Default'男', --设置默认约束
  [Age][int]
  )
  USE TEST
  INSERT INTO Temp(ID,Name,Age)VALUES(1, '彭友聪', 21)
  /****** Script for SelectTopNRows command from SSMS  ******/
  SELECT TOP (1000) [ID]
        ,[Name]
        ,[Sex]
        ,[Age]
    FROM [TEST].[dbo].[Temp]
  

默认约束.PNG

（2）在现有表中创建默认约束

• 其语法如下

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  DEFAULT constraint_expression[FOR column_name]
  --参数说明
  --table_name：要创建默认约束的表名称
  --constraint_name：默认约束名称
  --constraint_expression：默认值
  --例子
  USE TEST
  ALTER TABLE [Tb_student]
  ADD CONSTRAINT Default_age --设置默认约束
  DEFAULT 22 FOR 年龄
  

5.2、修改默认约束

• 修改表中某列 Default 约束使用的表达式，必须首先删除现有的 Default 约束，然后使用新定义重新创建，才能修改 Default 约束。

5.3、删除默认约束

• 删除默认约束的语法如下：

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name[,...n]
  
  --例子
  USE TEST
  ALTER TABLE Temp
  DROP CONSTRAINT Default_Sex
  

6、外键约束

6.1、创建外键约束

（1）在创建表时创建

CREATE TABLE Pyc(
    [ID][int],
    [Wage][money],
    CONSTRAINT FKEY_ID
    FOREIGN KEY(ID)
    REFERENCES Temp(ID)   --外键约束
    )

（2）在现有表中创建

• 语法如下

  ALTER TABLE table_name
  ADD CONSTRAINT constraint_name
  [FOREIGN KEY]{(column_name[,..n])}
  REFERENCES ref_table[(ref_column_name[,...n])]
  

• 参数说明如下表

  参数	描述
  table_name	要创建外键的表的名称
  constraint_name	外键约束的名称
  FOREIGN KEY REFERENCES	为列中的数据提供引用完整性的约束。ROFEIGN KEY 约束要求列中的每个值在被引用的表中对应的被引用列都存在；FOREIGN KEY 约束只能引用被引用表中为 PRIMARY KEY 或 UNIQUE 约束的列或被引用表中在 UNIQUE INDEX 内引用的列。
  ref_table	FOREIGN KEY 约束所引用的表名
  (ref_column_name[,...n])	FOREIGN KEY 约束所引用的表中的一列或多列

• 例子：

  USE TEST1
  ALTER TABLE Tb1
  ADD CONSTRAINT Fkey_ID
  FOREIGN KEY(ID)
  REFERENCES Agent(ID)
  

6.2、修改外键约束

• 修改表中的外键约束，必须先删除现有的外键约束，然后再用新定义创建外键约束。

6.3、删除外键约束

• 语法如下：

  ALTER TABLE table_name
  DROP CONSTRAINT constraint_name[,..n]
  
  --例子
  USE TEST
  ALTER TABLE Pyc
  DROP CONSTRAINT FKEY_ID
  

二、关系的创建与维护

• SQL Server 2017 是一个关系数据库管理系统，当数据库中包含多个表时，需要通过主关键字来建立表间的关系，使各表之间协调工作。

• 关系是通过匹配键列中的数据而工作，而键列通常是两个表间具有相同名称的列，在数据表间创建关系可以显示某个表中的列连接到另外一个表中的列。

• 表与表之间存在如下关系

  1）一对一关系

  2）一对多关系

  3）多对多关系

1、一对一关系

• 一对一关系是指表 A 中的一条记录确实在表 B 中有且只有一条相匹配的记录。
• 此关系有以下特点
• 1）分割一个含有许多列的表。
• 2）出于安全考虑而隔离表的某一部分。
• 3）存储可以很容易删除的临时数据，只需要删除表即可删除数据这些数据。
• 4）存储只应用于主表子集的信息。
• 5）如果两个相关列都是主键且具有唯一约束。

1.1、创建一对一关系

• 1）启动 SQL Server Management Studio，并连接到 SQL Server 2017 中的数据库
• 2）选定需要创建表关系的数据库
• 3）选定需要建立关系的数据表，右键选择“设计”命令，打开表设计窗口，右键单击 ID 字段，在弹出的快捷菜单中选择“关系”命令，打开“外键关系”对话框
• 4）在外键关系对话框中，单击常规下面的“表和列规范”文本框中的浏览按钮，打开“表和列”对话框，在该对话框设置关系名及主外键的表
• 5）在“表和列”对话框，单击确定按钮；外键关系对话框中单击关闭。

表列关系.PNG

• 一对多、多对多的关系的创建类似上述方法。