数据库表中的各种约束

约束是通过限制列中数据、行中数据和表之间数据来保证数据完整性的非常有效的方法。

约束可以确保把有效的数据输入列中国和维护表和表之间的特定关系。

其中，列约束是针对表中一个列的约束，表约束是针对表中一个或多个列的约束。

在SQL Server中有6种约束：

主键(primary key)约束、唯一性(unique)约束、外键(foreign key)约束、检查(check)约束、默认值(default)约束和是否为空(null和not null)约束。

1. 主键约束

主键约束是在表中定义一个主键值，这是唯一确定表中一行记录的标识符。

在所有的约束类型中，主键约束是最重要的一种约束类型，也是使用最广泛的。

一个表最多只能有一个主键，主键列不允许为空。

主键可以定义在一列上，也可以定义在多列的组合上。

当定义在多列上时，虽然某一列中可以有重复的数据，但是这个组合的数据是不能重复的。

(1)创建表时定义主键

在ichi数据库中创建数据表STUDENT，包括属性(ID、NAME、AGE、SEX)，其中ID定义为主键。

USE ichiDataBase

GO

CREATE TABLE STUDENT
  (
     ID   INT PRIMARY KEY,
     NAME NVARCHAR(200),
     AGE  NVARCHAR(50),
     SEX  NVARCHAR(50)
  ) 

在ichi数据库中创建数据表TEACHER，包含字段(ID、NAME、AGE、SEX)，其中(ID、NAME)的组合为主键。

USE ichiDataBase

GO

CREATE TABLE TEACHER
  (
     ID   INT ,
     NAME NVARCHAR(200),
     AGE  NVARCHAR(50),
     SEX  NVARCHAR(50),
	 CONSTRAINT PK_STUDENT PRIMARY KEY(ID,NAME)
  ) 

(2)修改表时添加主键

修改表时添加主键的语法格式如下：

ALTER TABLE table_name
ADD[constraint conatraint_name]
PRIMARY KEY [clustered|nonclustered]{(column_name)}

为STUDENT表中NAME设置主键

USE ichiDataBase

GO

ALTER TABLE STUDENT
  ADD CONSTRAINT PK_STUDENT PRIMARY KEY(NAME) 

从上面的例题中可以看出，创建约束时，可以指定约束的名称，否则系统会自动提供一个复杂的名称。

对于一个数据库来说，约束名称是唯一的。

一般来说，约束的名称应该按照这种格式：”约束类型简称_表名_字段名_代号”。

(3)删除主键

删除主键的语法格式如下：

ALTER TABLE table_name
DROP CONSTRAINT CONSTRAINT_NAME

删除STUDENT表中的主键

USE ichiDataBase

GO

ALTER TABLE STUDENT
  DROP CONSTRAINT PK_STUDENT 

2. 唯一性约束

唯一性约束用于指定非主键的一个列或多个列的组合值具有唯一性，以防止在列中输入重复的值。

也就是说如果一个数据表已经设置了主键约束，但该表中还包含其他的非主键列，也必需具有唯一性，为避免该列中的值出现重复输入的情况，可以使用唯一性约束。

(1)创建表时定义唯一性约束

在ichi数据库中创建数据表TEACHER1，包括属性(ID、NAME、SEX、AGE、IDCARD)，其中ID定义为主键，IDCARD要求数据是唯一的。

USE ichiDataBase

GO

CREATE TABLE TEACHER1
  (
     ID     INT PRIMARY KEY,
     NAME   NVARCHAR(20),
     SEX    NCHAR(1),
     AGE    INT,
     IDCARD CHAR(18) UNIQUE
  ) 

(2)修改表时添加唯一性约束

修改表时添加唯一性约束的语法格式如下：

ALTER TABLE table_name

Add CONSTRAINT CONSTRAINT_NAME UNIQUE

设置teacher1表中的name的值是唯一的。

ALTER TABLE teacher1

Add CONSTRAINT uq_student1 UNIQUE(name)

(3)删除唯一性约束

删除唯一性约束的语法格式与主键约束相同。

删除teacher1表中的uq_student1约束。

ALTER TABLE teacher1

DROP CONSTRAINT up_student1

(4)使用唯一性约束时应考虑的问题

唯一性约束所在的列允许取空值，但是主键约束所在的列不允许取空值。

一个表中可以有多个唯一性约束，可以把唯一性约束放在一个或多个列上，这些列必须是唯一的，但是，唯一性约束所在的列并不是表的主键列。

3. 外键约束

外键约束强制实现参照完整性，能够在同一个数据库的多个表之间建立关联，并维护表与表之间的依赖关系。

创建外键约束既可以由图形界面来完成，也可以由T-SQL语法来完成。

(1)在图形界面下建立表间的关系

在图形界面下建立表间的关系的步骤如下：

在SSMS中，展开ichi数据库，右击”数据库关系图”，在弹出的快捷菜单中选择”新建数据库关系图”命令，出现”添加表对话框”，如下图：

选择要添加关联的表，单击”添加”按钮完成表的添加，例如：添加TEACHER、STUDENT两个表，然后关闭”添加表”对话框。

拖动鼠标对不同表中相关的属性进行关联，如STUDENT表中的ID和TEACHER表中的ID，出现表的关联情况，如图所示：

单击”确定”按钮，建立两个表的关联，如图所示：

关系图建立完成后，保存并关闭此界面。

外键约束定义一个或多个列，这些列可以引用同一个表或另外一个表中的主键约束列或唯一性约束列。

在SQL Server中，表和表之间经常存在着大量的关系，这些关系都是通过定义主键约束和外键约束来实现的。

(2)创建表时定义外键约束

在ichi中创建scl表，包括属性(ID，COURSEID，SCORE)，并为SCL表创建外键约束，将ID字段和STUDENT表中的ID字段建立关联。

USE ichi

GO

CREATE TABLE scl
(

ID INT FOREIGN KEY REFERENCES STUDENT(ID),

COURSEID CHAR(5),

SCORE FLOAT

)

(3)修改表时添加外键约束

修改表时添加外键约束的语法如下：

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

FOREIGN KEY {(coulumn_name[,...n])}

REFERENCES{ref_table(ref_column[,...n])}

将ichi数据库中的TEACHER表和STUDENT表建立关联，关联字段是TEACHER表中的ID和STUDENT表中的ID

USE ichi

GO

ALTER TABLE STUDENT

ADD CONSTRAINT FK_T_S FOREIGN KEY(ID)

REFERENCES TEACHER(ID)

这些代码执行后”消息”窗口中将显示错误信息，如图所示：

发生错误的原因是TEACHER表中并没有把ID设置为主键，因此需要把TEACHER表中ID设置为主键后，再运行上述代码，即可完成外键的设置。

(4)删除外键约束

删除外键约束的语法格式与主键约束相同。

删除STUDENT表的外键约束

USE ichi

GO

ALTER TABLE STUDENT
  DROP CONSTRAINT FK_T_S 

(5)级联更新和级联删除

外键是双向的，无论用户在参照表中做了什么，外键都将检查被参照表，保持外键和主键的一致性，避免出现不完整的记录。

对于SQL Server而言，默认情况下如果被参照表中的某行数据的主键被引用，那么将不允许对该行删除或修改其主键值。

但是若希望在删除被参照表数据或修改被参照表中某个主键值的同时，自动删除参照表中对应的行，或将对应行的外键列同时修改，或将对应行的外键列设置为null等，那么将用到级联更新或删除。

级联更新和删除

• 级联更新：[on update { no action|cascade|set null|set default}]
• 级联删除：[on delete{no action|casacade|set null|set default}]

其中，on update表示级联更新，on delete表示级联删除。

no action是SQL Server的默认选项，表示不允许对被参照表执行删除或更新操作。

cascade是层叠操作，表示级联自动删除或级联自动更新参照表相关数据。

set null表示将参照表中的外键列数据设置为null，如果外键列定义了not null约束则不能使用此选项。

set default表示将参照表中的外键列数据设置为默认值，如果外键列未定义default值，则不能使用该选项。

在ichi中创建STUDENT2表，包括属性(ID，NAME，SEX)，并为STUDENT2表创建外键约束，将ID字段和TEACHER表中的ID字段建立关联，并设置级联更新和级联删除。

USE ichi

GO

CREATE TABLE STUDENT2
  (
     ID   INT FOREIGN KEY REFERENCES TEACHER(ID) ON DELETE CASCADE ON UPDATE CASCADE,
     NAME CHAR(20),
     SEX  NVARCHAR(10)
  ) 

可以修改TEACHER表中的学号或删除TEACHER表中的某行数据，体验级联更新和级联删除的效果。

4. 检查约束

检查约束用来限制用户输入某一列的数据，即在该列中只能输入指定范围的数据。

检查约束的作用非常类似于外键约束，两者都是限制某个列的取值范围，但是外键是通过其他表来限制列的取值范围，检查约束是通过指定的逻辑表达式来限制

列的取值范围。

例如·，描述学生”性别”列中可以利用检查约束来限制取值范围是”男”或”女”，描述学生”年龄”列中也可以利用检查约束来限制取值范围必须是大于0。

(1)创建表时定义检查约束

在ichi数据库创建TEACHER2表，包括属性(ID，NAME，SEX，AGE，IDCARD)，利用检查约束限制SEX的取值范围是”男”或”女”。

USE ichi

GO

CREATE TABLE TEACHER2
  (
     ID     INT PRIMARY KEY,
     NAME   NVARCHAR(20),
     SEX    CHAR(1) CHECK(SEX='男' OR SEX='女'),
     AGE    INT,
     IDCARD CHAR(18) UNIQUE
  ) 

(2)修改表时添加检查约束

修改表时添加检查约束的语法格式如下：

ALTER TABLE table_name

ADD CONSTRAINT CHECK_NAME CHECK(LOGICAL_EXPRESSION)

对ichi数据库中的STUDENT表进行修改，对AGE添加检查约束，要求输入的数据必须大于0。

USE ichi

GO

ALTER TABLE STUDENT
  ADD CONSTRAINT CK_STUDENT_AGE CHECK(AGE>0) 

(3)删除检查约束

删除检查约束的语法格式与主键约束相同。

删除上面修改所创建的约束

USE ichi

GO

ALTER TABLE STUDENT
  DROP CONSTRAINT CK_STUDENT_AGE 

一个列中可以定义多个检查约束，在执行INSERT或UPDATE操作时，检查约束会验证数据是否满足条件要求。

5. 默认值约束

当往数据表中插入数据时，如果某一列没有输入内容，那么可以用默认值约束在此列中输入一个默认值。

例如，在学生表的性别列中，可以用默认值约束设置数据”男”，当没有为性别提供数据时，默认值约束就会自动把”男”插入该列中。

(1)创建表时定义默认值约束

在ichi数据库中创建STUDENT1表，包括属性(ID，NAME，SEX，AGE)，把SEX的默认值设置为”男”

USE ichi

GO

CREATE TABLE STUDENT1
  (
     ID   INT,
     NAME NVARCHAR(20),
     SEX  NCHAR(1) DEFAULT '男',
     AGE  INT
  ) 

(2)修改表时添加默认值约束

修改表时添加默认值约束的语法格式如下：

ALTER TABLE TABLE_NAME
  ADD CONSTRAINT CONSTRAINT_NAME DEFAULT VALUES FOR COLUMN_NAME 

在ichi数据库中为STUDENT表中的SEX添加默认值”女”

USE ichi

GO

ALTER TABLE STUDENT
  ADD CONSTRAINT DF_SEX DEFAULT '女' FOR SEX 

(3)删除默认值约束

删除默认值约束的语法格式与主键约束相同。

删除上面修改例子的默认值约束

USE ichi

GO

ALTER TABLE STUDENT
  DROP CONSTRAINT DF_SEX 

(4)定义默认值约束应该注意的问题

定义默认值约束应该注意的问题如下：

• 定义的常量必须和此列的数据类型一致
• 默认值约束只能应用于INSERT语句
• 一列只能设置一个默认值约束，且默认值约束不能放在有INDENTITY属性的列上或者数据类型是Timestamp的列上，因为这两种列的内容由系统提供。