确保事务完整性

                                                  确保数据完整性

一、认识约束

约束是数据库中保证数据库里表中数据完整性的手段。录入数据库中的数据都有实际的意义，而约束的作用就是保证数据在实际业务中是有意义的，也就是减少脏数据的机会。

  1.1 什么是约束

          使用数据库约束就是保证数据库完整性的方法。数据库设计的完整性实际上是为了保证数据的正确性，那么为了保证书的正确性，在SQL Server 中设计的完整性主要由三个：实体完整性、区域完整性、参考完整性。

1.实体完整性

实体完整性针对表中行数据，要求表中的逐渐字段都不能为空或者重复值。如：每个人的身份证号都是唯一的。

2.区域完整性

区域完整性针对表中列数据，是保证数据库中在有效范围内，可以使用数据类型或CHECK约束来设置。如：性别只有男和女。

3.参照完整性

参照完整性，可以保证数据表中相关联的数据的正确性，使用外键就可以保证参照完整性。参照完整性是为了避免产生脏数据。

 1.2 约束的类型

         约束是一种强制实施数据库完整性的方法。利用约束可以定义列中允许中的规则，它的优先级高于DML触发器、规则和默认值。

1.主键约束（Primary Key）

主键约束在每个数据表中只能有一个，但是一个主键约束可以有多个列组成，通常把有多个列组成的主键又叫复合主键。主键约束可以保证主键列的数据没有重复值且值不为空。

2.外键约束（Foreign Key）

外键约束之所以被成为参照约束，是因为它主要用来把一个表中的数据和另外一个表中的数据进行关联，表与表之间的关联是为了保证数据库中数据的完整性。

3.唯一约束（Unique）

唯一约束和主键约束都是设置表中的列不能重复的约束，区别就是一个表中只能有一个主键约束，却可以有多个唯一约束，通常情况下设置唯一约束就是为了使非主键列没有重复值。唯一约束与主键约束的另一个区别就是如果表中的某一列中有空值，那么就不能设置为主键列，但可以设置成唯一约束。

4.检查约束（Check）

检查约束是为了指定表中列的取值范围的，该约束更适合完成与业务逻辑相关的限制。

5.非空约束（Not NULL）

非空约束是为了约束表中的列不允许为空的。

1.3约束的语法

     在SQL Server中创建和修改约束可以利用SSMS工具图形界面完成，也可以利用T-SQL来完成，下面介绍创建T-SQL常用的语法：

   [ CONSTRAINT  constraint_name ]

   {

       {PRIMARY KEY | UNIQUE}

          [CLUSTERED | NONCLUSTERED]

           (column [ASC | DESC][…])

          [WITH FILLFACTOR = fillfactor

          [WITH (<index_option>[,…])]

          [ON{ partition_scheme_name(partition_column_name…) | filegroup | “default”}]

          | FOREIGN KEY

                (column [,…])

          REFERENCES referenced_table_name [(ref_column[…])]

        | CHECK [NOT FOR REPLICATION ](logical_expression)

}

语法说明：

*CONSTRAINT项：关键词，表明指定约束。

*constraint_name项：约束名称，要求符合标识符规则，不能以“#”开头。

*PRIMARY KEY 项：表示主键约束，允许单列或多列组合。

*UNIQUE项：表示唯一约束。

*CLUSTERED | NONCLUSTERED项：表示为主键约束或唯一约束创建聚集或非聚集索引。

*WITH FILLFACTOR项：指定存储索引位置时应采用的每个索引页的填充程度。

默认值为0，可在0~100之间。

*[ON{partition_scheme_name(partition_column_name…)|filegroup|“default”}]项：表示为指定约束创建的索引的存储位置。

*FOREIGN KEY 项：外键约束。

*referenced_table_name项：外键约束引用表。

*CHECK项:检查约束。

*logical_expression项：属于CHECK约束的逻辑表达式。

二、使用约束

2.1利用SSMS创建主键约束。

2.2利用T-SQL增加主键约束

例子：利用T-SQL为Adventureworks数据库中的表test增加id字段和age字段的组合主键。

   USE AdventuerWorks

   GO

  

   ALTER TALBE [dbo].[test]

   ADD

   CONSTRAINT PK_test PRIMARY KEY NONCLUSTERED

( id,age)

代码说明：

*第一行表示当前数据库为Adventureworks数据库

*第四行表示修改dbo框架下的表test

*第五行表示为增加约束

*第六行表示创建主键约束PK_test非聚集索引

*第八行表示主键为id，age的组合

在查询编辑器中执行以上脚本，即可正常创建主键约束。

2.3利用SSMS创建外键约束

2.4利用T-SQL创建外键约束

例子：利用T-SQL为表orderinfo和表productinfo增加外键约束，关联列为orderinfo表中的ProductId和productinfo表中的ProductId列。

利用T-SQL创建约束需要使用查询编辑器，创建脚本如下：

USE AdventureWorks

GO

 

ALTER TABLE dbo.orderinfo

WITH CHECK

ADD CONSTRAINT FK_orderinfo_orderinfo

FOREIGN KEY(ProductId)

 REFERENCES dbo.productinfo (ProductId)

GO

代码说明：

*第1-2行表示使用数据库AdventureWorks

*第4行表示修改表dbo.orderinfo

*第5行表示创建约束时对数据进行检查

*第6行表示增加约束，名为FK_orderinfo_orderinfo

*第7行表示外键约束的外键列为ProductId

*第8行表示关联到表productinfo中的键列为ProductId

 

2.5利用SSMS工具创建CHECK约束

2.6利用T-SQL增加CHECK 约束

 例子：要求利用T-SQL创建

  创建脚本如下：

  USE AdventureWork

  GO

 

  ALTER TABLE dbo.orderinfo

  WITH NOCHECK

  ADD CONSTRAINT CK_orderinfo_orderouantity_se

  CHECK

    (OrderQuantity>0 And OrderQuantity<100)

  GO

  代码说明：

*第1-2行表示使用数据库AdventureWorks

*第4行表示修改表dbo.orderinfo

*第5行表示在创建或重新启用时不检查现有数据。如果表中已有的数据不符合该约束，会导致约束创建失败，所以要求在创建时不检查现有约束是否符合约束条件。

*第6行表示添加约束CK_orderinfo_orderouantity_se

*第7行表示约束为CHECK约束

*第8行表示要求检查OrderQuantity值在0-100之间。

5.7利用SSMS删除约束

 

三、事务的使用

3.1什么是事务

   食物在数据库中主要保证数据库的一致性，防止出现错误数据。在事务内的语句集合被看做成一个单元，语句集中一旦有一条失败，那么所有的都会失败。事务是日常编程中不可避免要接触的一部分。

SQL Server中事务的基本控制与具有如下几个：

 

*BEGIN TRANSACTION :表示本地事务的开始

*BEGIN DISTRIBUTED TRANSACTION:表示一个由Microsoft分布式事务处理协调管理的T-SQL分布式事务的起始。

*COMMIT TRANSACTION :表示事物的提交

*ROLLBACK TRANSCATION:事务的滚回。可以滚回到事务的起点，也可以滚回到事务的保存点。

3.2事务的特性

  事务是构成单一逻辑工作单元的操作集合，具有ACID特性，即ATOMIC（原子性）、CONSISTENT(一致性)、ISOLATED(隔离性)、DURABLE(持久性)，

只有具备以上4个特性才能称为一个事务。

（1）原子性：事务的原子性是指，事务中程序是数据库的逻辑工作单位，他对事物的全部修改要么全部执行，要么全不执行。原子性意味着不可分割，它是一个整体。

（2）一致性：事务的一致性是指，事务执行的前后数据库都必须处于一致状态，它是相对脏读而言的。只有在事务完成后才能被所有使用者看见，保证了数据的完整性。

（3）隔离性：隔离性是指并发的事物之间不能相互干扰，也就是说一个事物操作的数据不会被其他事务看到和操作。

（4）持久性：持久性是指一旦事务提交完成，那么这将是对数据永久性的修改，即使被修改后的数据遭到破坏，也不会出现回到修改之前的情况。

 3.3事务的模式类型

     1）显式事务

        所谓显式事务就是通过BEGIN TRANSACTION 语句来显式启动事务。并由COMMIT TRANSACTION 语句进行事务提交。

      BEGIN TRANSACTION 之后的DML操作都在一个事务之中，一旦有错，事务会进行滚回，将清除BEGIN TRANSACTION 之后的所有操作，回到原点。语法结构如下：

  BEGIN TRANSACTION

     [{transaction_name |@tran_name_variable}

      [WITH MARK [‘description’]]

   *BEGIN项：开始事务关键词。

   *TRANSACTION:和BEGIN一同表示事务开始

   *transaction_name项：事务名称。

   *@tran_name_variable项：变量名称，将接受一个事务名称。

   *WITCH MARK [‘description’]项：在日志中标记事务。

     2）自动提交事务

        自动提交事务时SQL Server数据库的默认模式。该类型不需要开发人员手工做任何操作，每个单独的T-SQL语句都在其完成后自动提交。

     3）隐式事务

        隐式事务需要利用T-SQL语句打开才能使用，打开隐式事务的语句是：

SET IMPLICIT_TRANSACTIONS ON

一旦隐式事务被打开，数据库实例第一次执行ALTER TABLE 、INSERT、CREATE、OPEN、DELETE、REVOKE、DROP、SELECT 、FECTCH等语句时，会自动开始一个新事务。

 3.4事务的保存点

    某种情况下业务过于复杂，需要很多的操作才能实现某项业务，开发人员为了避免每次石板都回滚所有数据，而选择事务的保存点，保存点为开发人员提供了回滚部分事务的机制。

语法格式：

SAVE TRANSACTION savepoint_name

ROLLBACK TRANSACTION savepoint_name

四、并发控制

前面介绍的都是单一用户使用数据库的情况，实际上，数据库是一个多用户系统，在同一时间允许多个用户访问。当多个用户同时访问同一数据时，为了保证数据的准确性，将对事务进行并发控制。

4.1并发访问的问题

并发访问数据时，如不加以控制，将可能出现以下情况：丢失更新、脏读、不一致的分析和幻像读。

1）丢失更新数据

当两个或两个以上的事务作用在相同的数据时，更新的数据会丢失的问题出现。因为每个事物都无法知道其他事务的存在，而后面的更新会覆盖前面更新的数据，这会导致数据丢失，此时读取的数据很可能不是一个正确的、最新的数据。

2）脏读

如果一个事务正在更新数据，而其他事务此时选择这些数据，则会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认，并且有可能由更新此行的事务所更改。这些数据被称为“脏数据”。

3）非重复读

同一事务多次访问同一行数据，但却得不到相同的数据，被称为”非重复读”.

4）幻象读

当一个事务访问符合查询的数据时（事务未提交），此时，如果另一个事务增加符合查询数据，那么第一个失误再次查询数据时会出现和第一次查询结果不同的情况，这种情况叫“幻象读”.

 

的避免此类问题出现的一个方法是采用并发控制技术，利用锁方法等来保证数据的正确性。

          4.2 SQL Server中的锁

             锁模式：共享锁（S锁）、更新锁（U锁）、排他锁（X锁）、意向锁、构架锁、大容量更新锁和键范围锁。

          4.3查看活跃事务

            相关语法：

             DBCC OPENTRAN

             [

                 ( [‘database_name’ | database_id | 0])

                 { [WITH TABLERESULTS][,[NO_INFOMSGS]]}

             ]

            语法说明：

             *DBCC OPENTRAN:关键词

             *database_name:数据库名，如果未指定，为当前数据库

             *database_id:数据库ID，如果为0则为当前数据库

             *WITH TABLERESULTS:以表格的方式指定结果

             *NO_INFOMSGS:取消信息性的消息

          4.4事务的隔离级别

          *READ UNCOMMITTED:未提交读取，

          *READ COMMITTED:已提交读取。

          *REPEATABLE READ:重复读取。

          *SERIALIZABLE:可序列化，它的隔离级别最高。

           4.5事务隔离级别的设置

             利用SET TRANSACTION语句设置：

             相关语法如下：

             SET TRANSACTION ISOLATION LEVEL

             {  READ UNCOMMITTED

                | READ COMMITTED

                | REPEATABLE READ

                | SERIALIZABLE

              }

五、事务的阻塞

如果一个事物在数据操作过程中锁住了某个数据库资源，而此时，另一个事务想访问此资源，则必须等待该资源解锁，这样就会发生阻塞。

六、死锁

死锁是在多用户或多进程状况下，为使用同一资源而产生的无法解决的争

用状态。当死锁发生时，通常有两个或更多的事务同时处于等待状态，而每个事务都在等待其它事务释放锁。

1）处理死锁

在SQL Server中为了避免出现死锁，可以通过锁监视器定期的对特定线程进行检测。如果检测到死锁，那么数据库引擎会对其中一个线程进行杀死操作。

2）预防死锁

正常来说，死锁不可能完全避免，只能减少发生的次数，下面给出了部分ＳＱＬ编写原则，用户宗逊这些原则编写ＳＱＬ脚本，就能有效地减少死锁的发生。

*尽量保证事务简短

   *事务尽量减少与人工交互

   *使用低级别隔离

   *访问资源的顺序要统一

七、索引

7.1．认识索引

 

   索引包含从视图或表中一个或多个列生成的键，以及映射到指定位置的存储位置的指针，索引需要占用数据库资源。利用索引可以快速查找指定数据，尤其在大数据量时。

索引有三类：

1）唯一索引

此类索引表示表中的每一个索引值只对应唯一的相关数据，由于它和主键的功能类似，所以，唯一索引常用于主键列中。

2）聚集索引

他会根据聚集索引键的顺序来存储表或视图中的数据，挤兑表中的物理数据按索引键值的顺序进行排列。

3）非聚集索引

该索引的索引键值的顺序与磁盘上的物理存储顺序不同。

 

索引在数据库中发挥的作用：

*可以非常有效的提高检索数据的速度

*可以保证数据唯一性

*可以减少查询中分组和排序的时间

*使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

      7.2 索引的创建

           (1)索引创建的关键语句是CREATE INDEX，其主要格式如下：

       CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]INDEX  index_name

       ON table_or_view_name

       (column[ASC | DESC][,…])

       [With

       [PAD_INDEX={ON | OFF}]

       [[,]FILLFACTOR=fillfactor][IGNORE_DUP_KEY ={ON|OFF}]

       [[,]DROP_EXISTING ={ON | OFF}]

       [[,]STATISTICS_NORECOMPUTE={ON | OFF}]

       [[,]SORT_IN_TEMPDB ={ON|OFF}]

        ]

       [ON filegroup]

1.语法说明：

*CREATE INDEX :创建索引关键词。

*UNIQUE:表名该索引是唯一索引。

*CLUSTERED:表示创建聚集索引。创建聚集索引会重新生成现有的非聚集索引。如果没有指定CLUSTERED,则创建非聚集索引。

*NONCLUSTERED:表示创建非聚集索引。

*index_name:表示索引的名称。索引的名称要求在表或视图中要唯一。

*column:为索引所作用的列，可以有多个。

*ASC|DESC:指定特定索引的升序或降序排序方向。

*PAD_INDEX:填充索引，默认为OFF.

*FILLFACTOR=fillfactor:顶一个百分比，指示在创建或重新生成索引其间，数据库引擎对各索引页的页级填充程度。

*IGNORE_DUP_KEY:指定在插入操作尝试向唯一索引插入重复键值时的错误响应。

*DROP_EXISTING:指定应删除并重新生成已命名的先前存在的聚集索引或非聚集索引。

*STATISTICS_NORECOMPUTE:指定是否重新计算分发信息。

*SORT_IN_TEMPDB:指定是否在tempdb中存储临时排序结果。

*ON filegroup:用于指定存放索引的文件组。

例子：创建唯一非聚集索引

USE Test

GO

 

CREATE UNIQUE INDEX IND_USERNAME

ON Newuser(username)

(2)索引的管理

A.查看索引：sp_helpindex [@objname]=‘name’

[@objname]=‘name’用于指定当前数据库中表或视图的名称。

B.禁用索引：

ALTER INDEX IND_USERNAME

ON Newuser

DISABLE

C.重新启用索引：

ALTER INDEX IND_USERNAME

ON Newuser

REBUILD

D.删除索引

DROP INDEX index_name

E.索引的重命名

Sp_rename old_index_name,new_index_name,INDEX