T-SQL复习08--数据完整性(约束)

• 数据完整性
  • 概念
  • 类型
  • 约束
• 域完整性类型
  • DEFAULT约束
    • 为现有表添加DEFAULT约束
    • 创建表时添加DEFAULT约束
  • CHECK约束
    • 创建表时添加CHECK约束
    • 为现有表添加CHECK约束
    • 删除约束
  • NULL约束
    • 创建表时指定是否允许为NULL
    • 修改现有表的字段是否允许为NULL
• 实体完整性类型
  • primary key约束
    • 创建表时指定主键
    • 给现有的表添加主键
  • UNIQUE约束
    • 创建表时同时创建UNIQUE约束
    • 给现有的表添加UNIQUE约束
• 引用完整性类型
  • FOREIGN KEY约束
• 总结

数据完整性

概念

数据完整性是指存储在数据库中数据的准确性和可靠性，它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的

类型

• 域完整性：域完整性指特定列的项的有效性
• 实体完整性：要求表中的所有行具有唯一的标识，例如主关键字值
• 引用完整性：确保量表之间的关系在更新和删除期间保持同步

  约束

  使用不同的约束强制数据完整性。约束时重要的数据库对象

域完整性类型

DEFAULT约束

指定列的默认值

为现有表添加DEFAULT约束

示例：

USE SCHOOL
GO
ALTER TABLE dbo.STUDENT
ADD CONSTRAINT DEF_SEX  --添加的约束名称
DEFAULT '男'  --约束类型及约束的值
FOR SEX  --添加约束的列
GO

创建表时添加DEFAULT约束

示例：

USE SCHOOL
GO
CREATE TABLE TEMP_DEFAULT
(
Id INT NOT NULL,
JOB NVARCHAR(100) DEFAULT '.NET CORE DEV' NOT NULL
)
GO

注：每一列只能有一个DEFAULT约束，不能用于IDENTITY属性的列，若默认值长度大于该字段允许的字符空间，则插入到该列的值会被截断

CHECK约束

限制列可接受的值，控制列值的范围，检车列值

CHECK约束默认检查现有数据和所有新数据，使用WITH NOCHECK中检查新数据

创建表时添加CHECK约束

示例：

USE SCHOOL
GO
CREATE TABLE TEMP_CHECK
(
ID INT NOT NULL,
JOB NVARCHAR(100) CHECK(JOB='JAVA' OR JOB='C#') NOT NULL
)
GO

为现有表添加CHECK约束

示例：

USE SCHOOL
GO
ALTER TABLE dbo.STUDENT --[WITH NOCHECK] 添加WITH NOCHECK则指检查新数据，不检查表里原有数据
ADD CONSTRAINT CHECK_AGE  --指定约束名称
CHECK(AGE>=0 AND AGE<=150) --指定约束访范围
GO

删除约束

示例：

USE SCHOOL
GO
ALTER TABLE dbo.STUDENT
DROP CONSTRAINT CHECK_AGE --约束名称
GO

NULL约束

指定列是否允许NULL，空值(或NULL)不同于0、空白或长度为0的字符串(如””)。NULL的意思是没有输入

创建表时指定是否允许为NULL

示例：

USE SCHOOL
GO
CREATE TABLE TEMP_NULL
(
ID INT NOT NULL,
NAME NVARCHAR(100) NULL
)
GO

修改现有表的字段是否允许为NULL

示例：

USE SCHOOL
GO
ALTER TABLE dbo.STUDENT
ALTER COLUMN ID INT NOT NULL --NOT NULL修改为NULL则标识允许为NULL
GO

实体完整性类型

primary key约束

能唯一标识表中每一行的值的一列或一组列，这样的一列或多列称为表的主键。一个表只能有一个primary key约束，并且primary key约束中的列不允许空值，不允许重复。如果对多列定义了primary key约束，则一列中的值可能会重复，但来自primary key约束定义中所有列的任何值组合必须唯一。

创建表时指定主键

示例：

USE SCHOOL
GO
CREATE TABLE TEMP_PK
(
ID BIGINT PRIMARY KEY(ID),
NAME NVARCHAR(100) NOT NULL
)
GO

给现有的表添加主键

示例：

USE SCHOOL
GO
ALTER TABLE dbo.STUDENT
ADD CONSTRAINT PK_ID --约束名称
PRIMARY KEY(ID) --指定主键列
GO

UNIQUE约束

确保在非主键列中不输入重复的值，可以对一个表定义多个UNIQUE约束，但只能定义一个PRIMARY KEY约束，UNIQUE约束允许一个NULL值，而PARMARY KEY约束不允许有NULL值

创建UNIQUE约束时，同时会创建一个同名的非聚集索引，当插入或修改数据时，UNIQUE约束自动执行校验数据

创建表时同时创建UNIQUE约束

示例：

USE SCHOOL
GO
CREATE TABLE TEMP_UNIUQE
(
ID INT NOT NULL,
NAME NVARCHAR(20) CONSTRAINT UNIQUE_NAME UNIQUE(NAME) NULL
)
GO

给现有的表添加UNIQUE约束

示例：

USE SCHOOL
GO
ALTER TABLE dbo.STUDENT
ADD CONSTRAINT UNIQUE_NAME
UNIQUE(NAME)
GO

引用完整性类型

FOREIGN KEY约束

用于建立和加强两个表数据之间的链接，可以是一列或多列，称为表的外键(FK)，一个表可以有多个FOREIGN KEY约束，选作外键必须与其他对应的主键列具有相同的数据类型，每个键中列的数必须相等，即如果主键是一列，则外键是一列，如果主键是多列，则外键对应也是多列。FOREIGN KEY约束不仅可以与另一个表的PRIMARY KEY约束相关联，还可以定义为引用另一个表的UNIQUE约束列

主键要先创建好才能创建外键，FOREIGN KEY约束不能自动创建索引

外键约束的主要目的时控制可以存储在外键表中的数据，同时它也可以控制对主键表中数据的更改

示例：

USE SCHOOL
GO
ALTER TABLE dbo.GRADE
ADD CONSTRAINT FK_STUDENT_ID  --外键约束名称
FOREIGN KEY(STUDENTID)  --指定当前表哪一列是外键
REFERENCES dbo.STUDENT(ID) --这个外键对应的是哪个表的主键
GO

总结

• DEFAULT、CHECK、NULL约束是针对表中的列进行完整性的控制，因此叫做域完整性
• PRIMARY KEY、UNIQUE约束是针对行限制数据行的唯一性，因此叫做实体完整性
• FOREIGN KEY约束是针对表与表之间的关系控制数据完整性，因此叫参照完整性