SQL_(数据完整性)_主键_外键_unique_check_primary_

1、-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20051Lecture 4数据完整性-目标要求：理解数据完整性的概念、种类和作用，熟悉数据完整性的实现方法。会进行实体完整性、域完整性、参照完整性进行控制。（主键外键约束创建管理）理解索引的概念、种类和作用，掌握索引创建、管理方法（SSMS、SQL语句）。-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20052数据完整性 在SQL Server 2005中，根据数据完整措施所作用的数据库对象和范围不同，可以将数据

2、完整性分为以下几种： 1、实体完整性（主键、唯一性约束） 2、参照完整性（外键约束） 3、域完整性（check、默认值约束） 4、用户定义的完整性-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20053主键(PRIMARY KEY)约束 主键约束使用数据表中的一列数据或者多列数据来唯一的标识一行数据。也就是说，在数据表中不能存在主键相同的两行数据，而且主键约束下的列不能为空（NULL），在创建表时，最好每个数据表都拥有自己唯一的主键，主键也可以由几个列组合成。每个表只能有一个主键。 不能定义TEXT或IMAGE数据类型的字段列

3、为主键。-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20054SSMS创建主键的步骤 在创建表结构的时候，选定列名或列名组合（shift键）,在所选列上右击弹出相应菜单，如右图所示。 单击有钥匙图标的菜单项。 创建成功后该列名前出现一个小钥匙形状的图标如右图所示。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20055创建表时定义主键CREATE TABLE Tb_Class( ClassId char(8) PRIMARY KEY, ClassName

4、varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber int)-CREATE TABLE Tb_Class( ClassId char(8) NOT NULL, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber int, CONSTRAINT PK_ClassId PRIMARY KEY (ClassId,ClassName)-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Serv

5、er 20056向已有表中添加主键 先创建一个无主键的表CREATE TABLE Tb_Class( ClassId char(8) NOT NULL, ClassName varchar(30) NOT NULL, DeptId char(2) NOT NULL, ClassStuNumber int) 再给相应的列（ ClassId ）添加主键约束ALTER TABLE Tb_ClassADD CONSTRAINT PK_ClassId PRIMARY KEY (ClassId)- 删除主键约束ALTER TABLE Tb_ClassDROP CONSTRAINT PK_ClassId P

6、RIMARY KEY (ClassId)-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20057外键（Foreign Key）约束 外键约束定义了表之间的关系，主要用来维护两个表之间的一致性。出于某种关联的需要，当一个表需要引用另一个表的主键主键作为自己的一个字段时，我们称这个引用来的字段为外键外键。 这样，当主键更新或删除时，其它所有表中与这个主键关联的外键关联的外键也将被相应的更新或删除。当向外键所在的表插入或更新数据（外键字段）时，如果与外键表相关联的主键表的主键主键中无与插入或更新的外外键键有相同的值时，系统会报错并拒

7、绝插入或更新数据。 不能定义TEXT或IMAGE数据类型的列为外键。-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20058外键约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 20059外键约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200510在创建表时定义外键约束CREATE TABLE Tb_Dept( DeptId char(2) PRIMARY KEY, DeptName v

8、archar(16) NOT NULL)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL, CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId)-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200511上述创建外键约束的语句可以简化为如下形式：-CREATE TABLE Tb_Stude

9、nt( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept)-从添加外键约束时只需添加REFERENCES关键字和被引用的表名。这里需要注意的是，外键列和被引用表中的主键列应该名称相同，且具有相同的列数据类型。在创建表时定义外键约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200512级联更新、删除CREATE TABLE Tb_Student( StuId char(10) PRI

10、MARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept ON UPDATE CASCADE ON DELETE CASCADE)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL, CONSTRAINT FK_DeptId FOREIGN KEY (DeptId) REFERENCES Tb_Dept (DeptId) ON UPDATE

11、CASCADE ON DELETE CASCADE)-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200513添加和删除外键约束 先创建一个无外键的表CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL) 再给相应的列（ DeptId ）添加外键约束ALTER TABLE Tb_StudentADD CONSTRAINT FK_DeptId FOREIGN KEY (DeptI

12、d) REFERENCES Tb_Dept (DeptId) - 删除外键约束ALTER TABLE Tb_StudentDROP CONSTRAINT FK_DeptId-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200514SSMS设置外键约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200515SSMS设置外键约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200516SSMS设

13、置外键约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200517外键约束的作用 修改父表主键时检查 删除父表记录时检查 在子表中插入数据时检查（外键）-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200518参照完整性（外键约束） 参照完整性是指两个表的主关键字（PRIMARY KEY）和外关键字（FOREIGN KEY）的数据要对应一致。它确保了有主关键字的表中对应其它表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据

14、库中扩散。 参照完整性是建立在外关键字和主关键字之上的。这样的一致性要求不能引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。在被参照表（主表）中，当其主键值被其他表所参照时，该行不能被删除也不允许改变。在参照表（从表）中，不允许参照不存在的主键值。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200519唯一性（UNIQUE）约束 可以使用 UNIQUE 约束确保在非主键列中不输入重复的值。尽管 UNIQUE 约束和 PRIMARY KEY 约束都强制唯一性，但想要强制一列或多列组合（

15、不是主键）的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束。 可以对一个表定义多个 UNIQUE 约束，但只能定义一个 PRIMARY KEY 约束。 而且，UNIQUE 约束允许 NULL 值，这一点与 PRIMARY KEY 约束不同。不过，当与参与 UNIQUE 约束的任何值一起使用时，每列只允许一个空值。 FOREIGN KEY 约束可以引用 UNIQUE 约束。-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200520 创建表时，可以创建 UNIQUE 约束作为表定义的一部分。如果表已经存在，

16、可以添加 UNIQUE 约束（假设组成 UNIQUE 约束的列或列组合仅包含唯一的值）。一个表可含有多个 UNIQUE 约束。 若要修改 UNIQUE 约束，必须首先删除现有的 UNIQUE 约束，然后用新定义重新创建。 默认情况下，向表中的现有列添加 UNIQUE 约束后，SQL Server 2005 Database Engine 将检查列中的现有数据，以确保所有值都是唯一的。如果向含有重复值的列添加 UNIQUE 约束，数据库引擎 将返回错误消息，并且不添加约束。 除非显式指定了聚集索引，否则，默认情况下将创建唯一的非聚集索引以强制执行 UNIQUE 约束。 唯一性约束-江阴职业技术学

17、院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200521创建表时创建UNIQUE约束CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL UNIQUE NONCLUSTERED, DeptId char(2) NOT NULL REFERENCES Tb_Dept)-CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId ch

18、ar(2) NOT NULL REFERENCES Tb_Dept, CONSTRAINT UK_StuName UNIQUE (StuName)-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200522创建表结束后添加、删除UNIQUE约束CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept)-添加UNIQUE约束ALTER TABLE

19、Tb_StudentADD CONSTRAINT UK_DepName UNIQUE (DeptName)-删除UNIQUE约束ALTER TABLE Tb_Student DROP CONSTRAINT UK_DepName-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200523SSMS创建惟一约束 右击表名称右击表名称，选择快捷菜单中的“设计设计”，进入表结构修改状态。单击工具栏处工具栏处“管理索引和键管理索引和键”按钮按钮，进入惟一约束设置界面，单击添加按钮，定义类型为“唯一键”选定惟一约束的列，为惟一约束定义标识名，

20、完成后关闭窗体。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200524默认值约束 如果插入行时没有为列指定值，默认值则指定列中使用什么值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。 若要应用默认值，可以通过在 CREATE TABLE 中使用 DEFAULT 关键字来创建默认值定义。这将为每一列分配一个常量表达式作为默认值。 默认值必须与要应用 DEFAULT 定义的列的数据类型相配。例如，int 列的默认值必须是整数，而不能是字符串。 后续版本的 Microsoft SQL Server 将删

21、除该功能。请避免在新的开发工作中使用该功能，并应着手修改当前还在使用该功能的应用程序。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200525CREATE TABLE Tb_Student( StuId char(10) PRIMARY KEY, StuName char(8) NOT NULL, DeptId char(2) NOT NULL REFERENCES Tb_Dept, Sex char(2) NOT NULL DEFAULT M, BIRTHDAY SMALLDATETIME NOT NULL DEFAUL

22、T getdate()添加和删除默认值约束-ALTER TABLE Tb_StudentADD CONSTRAINT DEF_Sex DEFAULT M FOR Sex-ALTER TABLE Tb_Student DROP CONSTRAINT DEF_Sex默认值约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200526SSMS中设置默认值约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200527CHECK约束 通过限制列可接受的值，CHECK 约

23、束可以强制域的完整性。此类约束类似于 FOREIGN KEY 约束，因为可以控制放入列中的值。但是，它们在确定有效值的方式上有所不同：FOREIGN KEY 约束从其他表获得有效值列表，而 CHECK 约束通过不基于其他列中的数据的逻辑表达式确定有效值。例如，可以通过创建 CHECK 约束将 成绩列中值的范围限制为从 0 到 100 之间的数据。这将防止输入的成绩值超出正常的成绩范围。 可以通过任何基于逻辑运算符返回 TRUE 或 FALSE 的逻辑（布尔）表达式创建 CHECK 约束。对于上面的示例，逻辑表达式为：grade= 0 AND grade = 0 and CourseScore

24、= 100) CHECK (EmpId LIKE A-ZA-ZA-Z1-90-90-90-90-9FM OR EmpId LIKE A-Z-A-Z1-90-90-90-90-9FM) CHECK (TelNo IN (86022679, 86022235, 86022879, 86022886, 86028225) OR TelNo LIKE 8602210-90-9) CHECK (Salary BETWEEN 3000 AND 10000) CHECK (IS_MANAGER = 1 AND Sex = F ) CHECK (CASE WHEN Is_Manager 1 AND Sex =

25、 F THEN 1 ELSE 0 END = 0)-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200531添加和删除CHECK约束 添加CHECK约束 ALTER TABLE Tb_Student WITH NOCHECK ADD CONSTRAINT CK_Sex CHECK (Sex IN (M,F) ; 删除CHECK约束 ALTER TABLE Tb_Student DROP CONSTRAINT CK_Sex -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Ser

26、ver 200532使用SSMS创建CHECK约束 1、在数据库关系图数据库关系图中，右键单击将包含该约束的表，再从快捷菜单中选择“CHECK 约束”。 - 或对于将包含该约束的表，打开表设计器，在表设计器中右键单击，再从快捷菜单中选择“CHECK 约束”。 2、单击“添加添加”。 注意：注意： 如果希望为约束指定一个不同的名称，请在“约束名”框中键入名称。 3、在网格内的 “表达式表达式”字段中，键入字段中，键入 CHECK 约束的约束的 SQL 表达式。表达式。例如，若要将Tb_Student表的Sex列中的项限制为M和F，键入：代码Sex = M or Sex = F，或者Sex in

27、(M, F)。或者，如果要求 ZipCode 列中的项为 6 位数字，键入：代码ZipCode LIKE 0-90-90-90-90-90-9 注意：注意： 确保将任何非数字约束值包含在单引号 中。 4、展开表设计器类别以设置在何时强制约束。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200533使用SSMS删除CHECK约束1、在对象资源管理器中，右键单击具有 CHECK 约束的表，再单击“修改”。此时，将在表设计器中打开该表。2、在“表设计器表设计器”菜单菜单中，单击“CHECK 约束”。3、在“CHECK 约束”对话

28、框中，从“选定的 CHECK 约束”列表中选择约束。4、单击“删除”按钮。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200534使用SSMS删除CHECK约束-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200535CHECK约束 限制输入到一列或多列中的可能值,只有符合条件与格式的数据才能存放字段，通过检查输入表列的数据的值来维护值域的完整性。检查约束通过对一个逻辑表达式的结果进行判断来对数据进行检查，对于单独一列可使用多个CHECK约束，按约束创建的

29、顺序对其检查。 一个表可以定义多个检查约束，但每个CREATE TABLE语句只能为每列定义一个检查约束；在每次插入记录或修改记录时，检查约束会做相应检查；自动编号字段不能应用检查约束。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200536数据完整性总结数据完整性总结 PRIMARY KEY 约束约束 一个表只能包含一个 PRIMARY KEY 约束。 由 PRIMARY KEY 约束生成的索引不会使表中的非聚集索引超过 249 个，聚集索引超过 1 个。 如果没有为 PRIMARY KEY 约束指定 CLUSTERED

30、 或 NONCLUSTERED，并且没有为 UNIQUE 约束指定聚集索引，则将对该 PRIMARY KEY 约束使用 CLUSTERED。 在 PRIMARY KEY 约束中定义的所有列都必须定义为 NOT NULL。如果没有指定为空性，则加入 PRIMARY KEY 约束的所有列的为空性都将设置为 NOT NULL。-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200537 UNIQUE 约束约束 如果没有为 UNIQUE 约束指定 CLUSTERED 或 NONCLUSTERED，则默认使用 NONCLUSTERED。

31、每个 UNIQUE 约束都生成一个索引。UNIQUE 约束的数目不会使表中的非聚集索引超过 249 个，聚集索引超过 1 个。数据完整性总结数据完整性总结-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200538 FOREIGN KEY 约束约束 如果在 FOREIGN KEY 约束的列中输入非 NULL 值，则此值必须在被引用列中存在；否则，将返回违反外键约束的错误信息。 如果未指定源列，则 FOREIGN KEY 约束适用于前面所讲的列。 FOREIGN KEY 约束仅能引用位于同一服务器上的同一数据库中的表。跨数据库的引

32、用完整性必须通过触发器实现。 列级 FOREIGN KEY 约束的 REFERENCES 子句只能列出一个引用列。此列的数据类型必须与定义约束的列的数据类型相同。 表级 FOREIGN KEY 约束的 REFERENCES 子句中引用列的数目必须与约束列列表中的列数相同。每个引用列的数据类型也必须与列表中相应列的数据类型相同。数据完整性总结数据完整性总结-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200539 如果类型为 timestamp 的列是外键或被引用键的一部分，则不能指定 CASCADE、SET NULL 或 SE

33、T DEFAULT。 建议表中包含的 FOREIGN KEY 约束不要超过 253 个，并且引用该表的 FOREIGN KEY 约束也不要超过 253 个。有效的限制还是或多或少取决于应用程序和硬件。在设计数据库和应用程序时应考虑强制 FOREIGN KEY 约束的开销。 对于临时表不强制 FOREIGN KEY 约束。 FOREIGN KEY 约束只能引用所引用的表的 PRIMARY KEY 或 UNIQUE 约束中的列或所引用的表上 UNIQUE INDEX 中的列。数据完整性总结数据完整性总结-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQ

34、L Server 200540 DEFAULT 定义定义 每列只能有一个 DEFAULT 定义。 DEFAULT 定义可以包含常量值、函数、SQL-92 niladic 函数或 NULL。 DEFAULT 定义中的 constant_expression 不能引用表中的其他列，也不能引用其他表、视图或存储过程。 不能对数据类型为 timestamp 的列或具有 IDENTITY 属性的列创建 DEFAULT 定义。 如果别名数据类型绑定到默认对象，则不能对该别名数据类型的列创建 DEFAULT 定义。数据完整性总结数据完整性总结-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12

35、-13Microsoft SQL Server 200541 CHECK 约束约束 列可以有任意多个 CHECK 约束，并且约束条件中可以包含用 AND 和 OR 组合起来的多个逻辑表达式。列上的多个 CHECK 约束按创建顺序进行验证。 列级 CHECK 约束只能引用被约束的列，表级 CHECK 约束只能引用同一表中的列。 当列上存在一个或多个 CHECK 约束时，将验证所有限制。 不能在 text、ntext 或 image 列上定义 CHECK 约束。数据完整性总结数据完整性总结-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Serve

36、r 200542 其他约束相关信息其他约束相关信息 为约束创建的索引不能用 DROP INDEX 删除；必须用 ALTER TABLE 删除约束。 除了不能以数字符号 (#) 开头以外，约束名称还必须符合标识符规则。如果未提供 constraint_name，则将系统生成的名称分配给约束。约束名将出现在所有与违反约束有关的错误信息中。 如果某个表具有 FOREIGN KEY 或 CHECK CONSTRAINTS 及触发器，则将+在触发器执行前先检查约束条件。 若要获得关于表及其列的报表，请使用 sp_help 或 sp_helpconstraint 表名。 若要获得与表相关的视图和存储过程的

37、报表，请使用 sp_depends。 如果该列是计算列，则其为空性总是由数据库引擎 自动确定。若要查找此类型列的为空性，请使用带 AllowsNull 属性的 COLUMNPROPERTY 函数。 数据完整性总结数据完整性总结-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200543索引-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200544新华字典 拼音检索法 笔画检索法-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microso

38、ft SQL Server 200545索引 与书中的索引一样，数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引索引包含从表或视图中一个或多个列生成的键键，以及映射到指定数据的存储位置的指针指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。 索引是一个单独的、物理的数据库结构，它是依赖于表建立的，提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面即索引就存放在上面。用户建立索引能

39、够提高查询速度，但过多索引会占据过多的磁盘空间，一般来说创建索引的列：经常被查询的列；是外键或主键的列；值惟一的列。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200546索引的类型 （1）簇索引又叫聚集索引 簇索引在数据表中按照物理顺序存储数据。簇索引在数据表中按照物理顺序存储数据。因为在表中只有一个物理顺序，所以在每个表中只能有一个簇索引。在查找某个范围内的数据时，簇索引是一种非常有效的索引，因为这些数据在存储的时候已经按照物理顺序排好序了，行的物理行的物理存储顺序和索引顺序完全相同。存储顺序和索引顺序完全相同。 默认

40、情况下，SQL Server为PRIMARY KEY约束所建立的索引为簇索引。在语句CREATE INDEX中，使用CLUSTERED选项建立簇索引。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200547 （2）非簇索引 非簇索引是具有与表的数据完全分离的结构，它不会改变行的物理存储顺序，但是它是由数据行由数据行指针指针(指向表中数据行)和一个索引值（和一个索引值（一般为键）构成构成的。 （3）唯一索引 唯一索引可以确保所有数据行中任意两行的被索引列不包括NULL在内的重复值。如果是复合唯一索引(有多列)，则该索引可以确

41、保索引列中每个组合都是唯一的。唯一索引不允许有两行具有相同的索引值，在创建惟一索引时，如果该列上已经存在重复值，系统会报错。 索引的类型 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200548索引结构-B树 在 SQL Server 中，索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根根节点节点。索引中的底层节点称为叶节点叶节点。根节点与叶节点之间的任何索引级别统称为中间级中间级。 在聚集索引中，叶节点包含基础表的数据页。聚集索引中，叶节点包含基础表的数据页。根节点和叶节点包含

42、含有索引行的索引页。每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。 非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点差别在于以下两点： （1）基础表的数据行不按非聚集键的顺序排序和存储（物理顺序不改变）。（2）非聚集索引的叶层是由索引页而不是由数据页组成非聚集索引的叶层是由索引页而不是由数据页组成。 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200549数值-B树-江阴职业技术学院计算机系江阴职业技术学院计算机

43、系-2021-12-13Microsoft SQL Server 200550教工编码-B树-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200551-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200552填充因子 提供填充因子选项是为了优化索引数据存储和性能。当创建或重新生成索引时，填充因子值可确定每个叶级页上要填充因子值可确定每个叶级页上要填充数据的空间百分比填充数据的空间百分比，以便保留一定百分比的可用空间供以后扩展索引。例如，指定填充因子的值为 80

44、 表示每个叶级页上将有 20% 的空间保留为空，以便随着在基础表中添加数据而为扩展索引提供空间。 填充因子值是 1 到 100 之间的一个百分比。在大多数情况下，服务器范围的默认值服务器范围的默认值 0 是最佳选项是最佳选项。如果将填充因子设置为 0，将几乎最大限度地填充叶级页将几乎最大限度地填充叶级页，但是至少会留出再添加一个索引行的空间。使用此设置可有效使用叶级空间，但应保留一定的空间以便在不得不拆分页之前进行有限的扩展。-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200553 如果向已满的索引页添加新行如果向已满的索引

45、页添加新行，数据库引擎将把大约一半的行移到新页中，以便为该新行腾出空间。这种重组称为页拆分页拆分。页拆分可为新记录腾出空间，但是执行页拆分可能需要花费一定的时间，此操作会消耗花费一定的时间，此操作会消耗大量资源。大量资源。此外，它还可能造成碎片，从而导致 I/O 操作增加。正确选择填充因子值可提供足够的空间以便随着向基础表中添加数据而扩展索引，从而减少页拆分可能性。 尽管采用较低的填充因子值（非采用较低的填充因子值（非 0）可减少随着索引减少随着索引增长而拆分页的需求增长而拆分页的需求，但是索引将需要更多的存储空间，并且会降低读取性能。因此，指定一个不同于默认值的填充因子会降低数据库的读取性能

46、，而降低量与填充因子设置的值成反比。例如，当填充因子的值为 50 时，数据库的读取性能会降低两倍。读取性能降低是因为索引包含较多的页，因此增加了检索数据因为索引包含较多的页，因此增加了检索数据所需的磁盘所需的磁盘 I/O 操作操作。 填充因子-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200554比如：3个叶节点，每个节点可以存4个键值，总共有12个值- 25%pctfree : 节点1 节点2 节点3 1,2,3,等待插入值 8,9,10,等待插入值 12,13,18,等待插入值 - 0%pctfree : 节点1 节点2

47、 节点3 节点4 1,2,3,4 5,6,7,8 9,10,11,12 13,14,15,16 等待插入- 所以我们分析，如果是序列器生成的，那么就可以用0%pctfree方案，这样就只会牵涉到最后一个叶节点，而不需要很多拆分；而25%pctfree则比较合适对于新增比如11的，这样就可以运行的插入开销较小，直接插入节点2。填充因子-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200555 对于任何其键值不断增加的列对于任何其键值不断增加的列，也就是说，比如一个主键用序列器生成，只会增加键值到最大值，那么他几乎不需要中间接点插

48、入某个值，所以对于pctfree意义也不大，所以最好是设置一个非常低的PCTFREE（甚至可以为0）。因为这样就会只有那些最右方的叶节点块总是会被插入，从而使得最右方的叶节点块总是会被插入，从而使得树向右增长树向右增长。而左边的叶节点将一直为静止状态，因此没有必要使得这些块的任何部分为空（通过使用非零PCTFREE）填充因子-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200556SSMS创建索引 1、在对象资源管理器中，右键单击要对其创建索引的表，再单击“修改”。此时，将在表设计器中打开该表。（或表中选择索引，右键选择新建。

49、） 2、在“表设计器”菜单中，单击“索引/键”。 3、在“索引/键”对话框中，单击“添加”。 4、在“选定的主/唯一键或索引”列表中选择新索引，然后在右侧的网格中设置该索引的属性。 5、为索引指定任何其他设置，再单击“关闭”。保存表时将在数据库中创建该索引。-江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200557SSMS创建索引 -江阴职业技术学院计算机系江阴职业技术学院计算机系-2021-12-13Microsoft SQL Server 200558T-SQL语言创建非聚集索引和唯一非聚集索引 CREATE INDEX IX_StuName ON Tb_Student(StuName); GO CREATE NONCLUSTERED INDEX IX_StuName_Sex ON Tb_Student (StuName, Sex); GO CREATE UNIQUE INDEX UK_StuName