SQL Server 数据库之数据完整性

最新推荐文章于 2021-04-24 18:55:45 发布

June·DD

最新推荐文章于 2021-04-24 18:55:45 发布

阅读量6.6k

点赞数 10

分类专栏：数据库文章标签： SQL Server 数据完整性

本文链接：https://blog.csdn.net/dark_cy/article/details/103326359

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

目录
　　~ 简述

　　~ 实体完整性
　　　　· 主键约束
　　　　· 唯一性约束

　　~ 域完整性
　　　　· 空值约束
　　　　· 默认约束
　　　　· 检查约束

　　~ 引用完整性

　　~ 删除和禁用约束
　　　　· 删除约束
　　　　· 禁用约束

简述

　　数据完整性是指数据库中数据在逻辑上的一致性、准确性、有效性和相容性。它包括实体完整性、参照完整性和用户自定义完整性。而在 SQL Server 中，数据完整性分为实体完整性、域完整性和引用完整性三类。

实体完整性

　　实体完整性又叫行完整性，它是指表中的每一个记录都能唯一标识，且不存在重复的记录。可以通过主键约束和唯一性约束来实现实体完整性。

主键约束

　　主键约束强制要求被设置成主键的列取值不能为空，也不能重复。它通过PRIMARY KEY子句来实现主键的声明。可以在 CREATE TABLE 或 ALTER TABLE 语句中使用PRIMARY KEY子句，实现主键约束的创建、删除或修改。一个表中只能有一个主键，即只能设置一个主键约束对象。

　　代码示例

/*------------------------------------创建时，设置单属性主键*/
		CREATE TABLE Student
		(
		StudentID NCHAR(10) PRIMARY KEY,
		Name NCHAR(6),
		Sex NCHAR(2)
		)
		
/*------------------------------------创建时，设置联合主键*/
		CREATE TABLE Student
		(
		StudentID NCHAR(10),
		Name NCHAR(6),
		Sex NCHAR(2),
		CONSTRAINT PK_Name2 PRIMARY KEY(StudentID, Name) /*声明联合主键*/
		)
		/* PS：联合主键是由多个属性组成一个主键。主键只有一个，并不是多个 */
		/* PK_name 是主键约束对象的名字 */
		
		
/*------------------------------------修改时，设置单属性主键*/
		ALTER TABLE Student
			ADD myname NCHAR(4) PRIMARY KEY,
				mysex NCHAR(2)
				
/*------------------------------------修改时，设置联合主键*/
		ALTER TABLE Student
			ADD myname NCHAR(4),
				mystid NCHAR(4),
				mysex NCHAR(5),
				CONSTRAINT PK_name PRIMARY KEY(myname, mystid)

PS：主键约束会自动在指定列上创建一个唯一索引，默认是聚集索引，但可以指定主键对应的索引为非聚集索引。

唯一性约束

　　主键约束一个表只能有一个，但很多时候我们需要除了主键列以外的列没有重复值。这就用到了唯一性约束。唯一性约束与主键约束的效果基本相同，但一个表可以由多个唯一性约束。它通过 UNIQUE子句来实现唯一性约束的声明，可以在 CREATE TABLE 或 ALTER TABLE 语句中使用UNIQUE子句，实现主唯一性约束的创建、删除或修改。

　　唯一性约束可以允许值为 NULL，单不允许值重复，包括重复的NULL。

　　代码示例

/*------------------------------------创建时，设置唯一性约束*/
		CREATE TABLE Student
		(
		StudentID NCHAR(10) PRIMARY KEY,
		Name NCHAR(6) UNIQUE,
		Sex NCHAR(2),
		SchoolID NCHAR(2) UNIQUE,
		--这种创建方法，唯一性约束以多个键对象的形式存在--
		)

/*------------------------------------创建时，设置唯一性约束*/
		CREATE TABLE Student
		(
		StudentID NCHAR(10) PRIMARY KEY,
		Name NCHAR(6),
		Sex NCHAR(2),
		SchoolID NCHAR(2),
		CONSTRAINT ue_name UNIQUE(Name, SchoolID) 
		--唯一性约束以一个键对象的形式存在--
		)


/*------------------------------------修改时，设置唯一性约束*/
		ALTER TABLE Student
			ADD myname NCHAR(4) UNIQUE,
				mysex NCHAR(4),
		
/*------------------------------------修改时，设置唯一性约束*/
		ALTER TABLE Student
			ADD myname NCHAR(4),
				myscid NCHAR(2),
				mysex NCHAR(4),
				CONSTRAINT UN_name UNIQUE(myname, myscid)

PS：同主键约束一样，唯一性约束也会自动创建一个唯一性索引，但默认是非聚集索引，可以指定为聚集索引

域完整性约束

　　域完整性约束又叫列完整性约束，是指数据集对某一列是否有效和确定，是否允许为空值，通常通过空值约束、默认约束和检查约束来实现域完整性约束。

空值约束

　　即设置列是否可以为空值，通过 NULL或NOT NULL来声明列是否可以为空值，可以在CREATE TABLE 或 ALTER TABLE 语句中使用NULL或NOT NULL子句，实现空值约束的创建、删除或修改。

　　代码示例

/*------------------------------------创建时，设置空值约束*/
		CREATE TABLE Student
		(
		StudentID NCHAR(10) PRIMARY KEY,
		Name NCHAR(4) NOT NULL, --设置不能为空--
		Age INT NULL --设置可以为空--
		)


/*------------------------------------修改时，设置空值约束*/
		ALTER TABLE Student
			ADD Sex NCHAR(4) NULL

PS：主键约束会自动要求不能为NULL，相当于自动设置了 NOT NULL

默认约束

　　默认约束，即为列设置默认值。通过DEFAULT子句来为列设置默认值，可以在CREATE TABLE 或 ALTER TABLE 语句中使用DEFAULT子句，实现默认约束的创建、删除或修改。

　　代码示例

/*------------------------------------创建时，设置默认约束*/
		CREATE TABLE Student
		(
		StudentID NCHAR(10) PRIMARY KEY,
		Name NCHAR(6) DEFAULT('阿珍'),
		Sex NCHAR(2) DEFAULT('女'),
		)

/*------------------------------------创建时，设置默认约束*/
		CREATE TABLE Student
		(
		StudentID NCHAR(10) PRIMARY KEY,
		Name NCHAR(6),
		Sex NCHAR(2),
		CONSTRAINT Fe_name DEFAULT '女' FOR Sex
		)


/*------------------------------------修改时，设置默认约束*/
		ALTER TABLE Student
			ADD Hobby NCHAR(4) DEFAULT('吃')
	
/*------------------------------------修改时，设置默认约束*/
		ALTER TABLE Student
			ADD Hobby1 NCHAR(4),
				Hobby2 nchar(2),
				CONSTRAINT DF_name DEFAULT '玩' for Hobby1

PS：允许使用一些系统函数的返回值充当默认值

检查约束

　　检查约束就是为列设置取值范围，规定数据的值域。可以在CREATE TABLE 或 ALTER TABLE 语句中使用DEFAULT子句来实现检查约束的创建。

　　代码示例

/*------------------------------------创建时，设置检查约束*/
		CREATE TABLE myinto
		(
		Name NCHAR(4),
		Sex NCHAR(1) CHECK(Sex='男' or Sex='女'),--括号内可以是一个表达式--
		Hobby NCHAR(10) NULL,
		)

/*------------------------------------创建时，设置检查约束*/
		CREATE TABLE myinto
		(
		Name NCHAR(4),
		Sex NCHAR(1),
		Hobby NCHAR(10) NULL,
		CONSTRAINT CK_name CHECK(Sex='男' or Sex='女')
		--一个约束对象只能检查一个列--
		)


/*------------------------------------修改时，设置默认约束*/
		ALTER TABLE myinto
		ADD Lover NCHAR(2) CHECK(Lover='阿珍' or Lover='阿强')

/*------------------------------------修改时，设置默认约束*/
		ALTER TABLE myinto
		ADD Lover NCHAR(2),
			Lover_name NCHAR(1),
			CONSTRAINT CK_name CHECK(Lover='阿珍' or Lover='阿强')

PS：一个列上可以有多个检查约束，且检查约束可以参考本表中的其他列

引用完整性

　　引用完整性也成为参照完整性约束或外键约束等，它保证主键和外部键的关系总是得到维护，其中主键在被参照表中（也成为主键表），外部键在参照表中（也成为外键表）。

　　通过定义外键约束来实现引用完整性。外键约束定义一个列或多个列，这些列可以参照当前表或其他表的主键或UNIQUE约束列，通过设置外键约束可以实现表和表之间的依赖关系。外键约束可以在CREATE TABLE 或 ALTER TABLE 语句中使用FOREIGN KEY和REFERENCES子句创建。

　　外键约束有两种规则：更新规则（ON UPDATE）和删除规则（ON DELETE），两种规则又共用四种模式：不执行任何操作（NO ACTTON）、级联（CASCADE）、置空（SET NULL）和设置默认值（SET DEFAULT）。下面将一一介绍这四种模式。

不执行任何操作（NO ACCTTON）
这是默认值，当没有对两种规则指定模式时，就会默认模式为 NO ACTTON。在该模式下，要求外键列（参照列）不能插入或修改成主键列（被参照列）没有的值（删除不受影响）；主键表不能修改涉及外部键值记录的主键值，不能删除涉及外部键值的记录（插入不受影响）。
级联（CASCADE）
这种模式要求外键列不能插入或修改成主键列没有的值（删除不受影响），且随着主键值的修改，相应的外部键值同步修改，随着主键表行的删除，相应的外键表行也同步删除（主键表插入不受影响）。
置空（SET NULL）
该模式要求，外键列不能插入或修改成主键列没有的值（但删除不影响），且随着主键值的修改或删除（行），相应的外部键值置空（主键表插入不影响）。
设置默认值（SET DEFAULT）
该模式要求，外键列不能插入或修改成主键列没有的值（但删除不影响），且随着主键值的修改或删除（行），相应的外部键值被设置成默认值（主键表插入不影响）。
　　
值得注意的是，如果没有对外键列设置默认约束，则默认值为NULL，如果设置了默认值约束，则该默认值必须是被参照列中的某一个数据项，否则在插入默认值时会失败（外键列不能插入或修改成主键列没有的值）。

　　示例代码

/*------------------------------------创建时，设置外键约束*/
CREATE TABLE Course
(
Coid INT PRIMARY KEY,
Coname NCHAR(10),
Coclass NCHAR(15)
)
CREATE TABLE Student
(
Stid INT PRIMARY KEY,
Name NCHAR(10) NOT NULL,
Coid INT FOREIGN KEY REFERENCES Course(Coid) ON UPDATE SET NULL ON DELETE SET NULL
--定义外键约束，并设置成‘设置控制’模式--
)

/*------------------------------------修改时，设置外键约束*/
ALTER TABLE Student
ADD Coid INT,
CONSTRAINT CE_name FOREIGN KEY(Coid) REFERENCES Course(Coid) ON UPDATE CASCADE ON DELETE CASCADE
--设置成级联模式--

PS：如果删除的表设置有外键关系，则必须先删除参照表（外键表），再删除被参照表（主键表）

删除和禁止约束

删除约束

　　可以在ALTER TABLE语句中删除约束，但前提是你知道约束对象的名字。代码如下

ALTER TABLE Mytable
	DROP CONSTRAINT constraint_name

　　在创建约束约束时可以用CONSTRAINT子句定义约束对象名，对于没有定义的约束对象，系统会自动为其分配一个名字，其格式为：约束类型简称_表名_列名_代号 。

禁用约束

　　一般情况下，我们在 ALTER TABLE语句中对一个表添加约束后，系统会自动检查表中已经存在的数据是否满足该约束。但在一些特殊情况时，我们不想要改变表中已存在的数据，只想对新的数据进行约束，这时候就需要在已有数据上禁用约束。

　　想要禁止约束应用在已有数据上需要在创建约束的语句前加WITH NOCHECK，如下

ALTER TABLE Student
WITH NOCHECK --对已有数据禁用约束--
	ADD CONSTRAINT CK_name CHECK (stid > 10000)

PS：1、只能禁用检查约束（CHECK）和外键约束应用在已有数据上
　　 2、使用 WITH NOCHECK 创建的约束会对之后插入或更新的数据产生约束，包括对已有数据的更新
　　
　　
　　
　　
　　
　　