约束与索引

非空约束 Not Null

create table emp(emp_id nvarchar(9) not null,emp_name nvarchar(50) not null,)

当一列被创建时，可以使用not null约束，空值null 等于没有或未知道，该表的两个列不允许插入null或者不设置值。

 

主键约束 primary key

用于标识表中一行数据是唯一的，主键通常由一列组成，但也可以多列共同组成主键。主键是一个隐性约束

create table emp(emp_id nvarchar(9) not null primary key,emp_name nvarchar(50) not null,)alter table emp add constraint emp_name_pk primary key(emp_name)

改变一个表的约束

 

唯一约束 UNIQUE

是指约束在指定列上的值必须唯一，它和主键约束类似，当在一个表上的一列放置主键约束时，可以在另一列上放置唯一约束。主键比约束唯一多了数据提供顺序和在查询中用于连接表之外，主键约束和唯一约束没有太大区别。

create table emp(emp_id nvarchar(9) not null primary key ,emp_name nvarchar(50) not null unique,)

 

外键约束 foreign key (父子表)

create table emp_pay(emp_id nvarchar(9) not null,posititon varchar(15) not null,pay_rate nvarchar(50) not null, )goalter table emp_pay add constraint emp_id_fk foreign key(emp_id) references emp(emp_id);go

 

校验约束

用来检查输入到表中某一特定列的数据的有效性，一般应用程序在前台也要校验数据是否有效，数据库的校验提供了另一种实现数据的保护方式。

ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 40)add constraint chk_emp_zip check ( emp_zip = '050021')add constraint chk_emp_zip check ( emp_zip in ( '050021','050000',021111'))add constraint chk_emp_pay check (pay_rate > 12.50)

 

默认约束

ADD CONSTRAINT DF_stuAddress  DEFAULT ('地址不详') FOR stuAddress 

--添加默认约束（如果地址不填，默认为“地址不详”）

 

使用约束的要点

首先必须理解数据库中各个表之间的关系，看看哪些表依赖于别的表，哪些列依赖于别的列，哪些可以可空还是必须有数据

使用约束的次序

当使用create table命令创建约束时或者用alter table命令在表上添加约束时，必须以正确的顺序制定约束，例如必须在定义参照某个主键的外键时，必须先定义这个主键。如果想删除一个主键，必须先删除参照这个主键的所有外键。再譬如父子记录，必须首先有父记录，才能创建子记录，删除时必须先删除子记录，才能删除父记录。

在表上创建索引以提高速度——快速的获取数据

索引是一种基于表中数据的对象，与view不同，索引需要占用物理存储。它提供的数据顺序不同于属于在磁盘上的物理存储数据，特殊作用就是在表内重新排列记录的物理位置，索引基于表的一列或多列组合建立。通常在正确的字段上建立索引，可以使性能显著的提高，特别是在表之间的连接字段上建立索引。

技巧

对于较小的表，索引不会有大的性能改变；在数据变化幅度较大的（唯一值最多的）列上建立索引，会产生最大程度的性能改变，在带有索引的列上进行大量的插入、删除或更新会产生性能影响，应该及时地删除索引，完成操作后在建立索引。

复合索引

create index id_cmpd_index on bills(account_id,amount); 

--即使两个字段相结合，也只是建立了一个实际的索引。

UNIQUE 唯一性索引

由于主键中的每个值必须是唯一的，所以数据库将隐含的创建索引，主键和唯一约束都使系统生成索引。

 

聚簇索引

主键一般是应该设为聚集索引的，但现在有了新的认识，主键主要意义在于“唯一标识记录”，而索引主要是“提高数据库性能”，尤其是“聚集索引”。

 而现在我会注重数据库索引的建立，最关键的就是聚集索引，“聚集索引”与“非聚集索引”的理解很简单：“聚集索引”就是物理上的排列方式，而“非聚集索引”是使用索引表（相当于书的目录），使用索引之所以能提高性能，也就是“避免全表扫描”。

 因此一个表只能有一个“聚集索引”，因为在物理上，不可能会有“两种”物理存放方式吧。也真是如此“聚集索引”对查询性能的影响最大，所以要非常注意“聚集索引”的建立，我目前意识到、也是Freedk所推荐的“单据日期”作为聚集索引，我说的“单据日期”是指日常业务性的记录，一般都是会有一个日期字段的，而80%查询中都会用到此"日期字段",而且把它放在Where的第一个效果最佳，因为会在指定的区域内进行其他条件的查询，范围少了很多很多，性能效果提高明显。 

 至于其他的索引，这只要根据业务的需要，对于一些会进行查询的字段进行一定的索引，也就是尽可能的“避免全表扫描”就可以提高系统性能。当然过多的索引也会引起系统负担，想想要建那么多“目录”自然会增加负担了。

 还有“聚集索引”由于是物理存储的，性能上固然是提高了，但对于插入与删除，会引起数据的“移动”（在中间插入一条记录，会引起后半部分记录的后移），当然数据库有自己的存储机制，会采用“页”来进行分块存储，可以避免一定的“移动”，所以“聚集索引”最好是在“大部分”情况下可以“递增”，我们目前采用的“单据日期”也刚好是符合这个条件的。

避免全表扫描如果没有索引，当执行select或者更新的时候就会执行全表扫描，严重的影响sql的性能。应该分析数据库的执行过程，找到经常需要查询的字段(where子句的），将之使用索引，或者聚集索引（只能有一个聚集索引）以提供性能。 但有时全表扫描又是必须的：例如从一个表中几乎需要选择所有的表、修改表中的所有的行、表比较小。注意不要使用  %张%少使用 in or 语句这会使索引实效~