sqlserver元数据：表约束、表索引和表触发器

环境：

• window10 x64 专业版
• sqlserver2014

一、表约束

1.1 表约束概念和类型

六种类型的约束：

• 主键约束（创建时自动创建一个聚集唯一索引）
• 唯一约束（创建时自动创建一个非聚集唯一索引）
• 外键约束(级联更新、级联删除、设为空)
• 默认约束
• 检查约束
• 非空约束（概念上的，体现在列的属性上，元数据中没有单独存储的地方）

2.1 实验这些约束以及查询元数据

创建这些约束：

IF EXISTS(SELECT * FROM sys.tables WHERE name = N'student')
    DROP table student;
GO
IF EXISTS(SELECT * FROM sys.tables WHERE name = N'class')
    DROP table class;
GO
create table class(
id int primary key,
cno varchar(50),
name varchar(50),
createtime datetime default(getdate())
);
go

create table student(
	id int primary key,
	sno varchar(50) not null unique,
	name varchar(50) not null check(len(name)<5),
	score float not null default(-1),
	classid int
);
go

ALTER TABLE student ADD CONSTRAINT FK_student_class FOREIGN KEY(classid) REFERENCES class(id) ON DELETE set null;

IF EXISTS(SELECT * FROM sys.tables WHERE name = N'book')
    DROP table book;
GO
create table book(
	id int not null,
	name varchar(50) not null
);
go
ALTER TABLE book ADD CONSTRAINT PK_book primary key (id,name);

IF EXISTS(SELECT * FROM sys.tables WHERE name = N'student_book')
    DROP table student_book;
GO
create table student_book(
sid int,
bid int
);
go

ALTER TABLE student_book ADD CONSTRAINT UQ_student_book UNIQUE (sid,bid);
ALTER TABLE student_book ADD CONSTRAINT CK_student_book CHECK(sid is not null and bid is not null);

查看约束元数据：

• 查询默认约束

  -- 查询默认约束
  select 
  SCHEMA_NAME(sysd.schema_id) as schema_name,  
  syst.name as table_name,
  sysd.name,
  sysd.type,sysd.type_desc,
  sysc.name as column_name,
  sysd.create_date,sysd.modify_date,
  sysd.is_system_named,sysd.definition 
  from sys.default_constraints sysd 
  left join sys.tables syst on syst.object_id=sysd.parent_object_id
  left join sys.columns sysc on syst.object_id=sysc.object_id and sysc.column_id=sysd.parent_column_id
  where sysd.type='D' 
  and sysd.parent_object_id in (OBJECT_ID('class'),OBJECT_ID('student'),OBJECT_ID('book'),OBJECT_ID('student_book'))	
  

  输出：
  

• 查询检查约束

  --查询检查约束
  select 
  SCHEMA_NAME(sysc.schema_id) as schema_name,
  syst.name as table_name, 
  sysc.name,
  sysc.create_date,sysc.modify_date,
  sysc.type,sysc.type_desc,
  sysc.definition,sysc.is_system_named
  from sys.check_constraints sysc left join sys.tables syst on sysc.parent_object_id=syst.object_id
  where 
  sysc.parent_object_id in(OBJECT_ID('class'),OBJECT_ID('student'),OBJECT_ID('book'),OBJECT_ID('student_book'))
  

  输出：
  

• 查询主键约束和唯一约束

  -- 查询主键约束和唯一约束
  select sysk.object_id,SCHEMA_NAME(sysk.schema_id) as schema_name,sysk.name as constraint_name, syst.name as table_name,sysc.name as col_name,
  sysk.type,sysk.type_desc,
  sysk.create_date,sysk.modify_date,
  sysk.is_system_named
  from sys.key_constraints sysk 
  	left join sys.indexes sysi on sysk.name =sysi.name
  	left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
  	left join sys.columns sysc on sysic.object_id=sysc.object_id and sysic.column_id=sysc.column_id
  	left join sys.tables syst on sysi.object_id=syst.object_id
  where sysk.parent_object_id in(object_id('class'),object_id('student'),object_id('book'),object_id('student_book'))
  order by sysk.object_id
  

  输出：
  

需要注意的是：由于主键和唯一约束都可能有多列组成，所以上面输出的结果中object_id列会有重复的情况！

• 查询外键约束

  -- 查询外键约束
  select SCHEMA_NAME(sysfk.schema_id) as schema_name, sysfk.name,syst.name as tablename,sysc.name as colname,systr.name as ftablename,
  sysfk.create_date,sysfk.modify_date,sysfk.is_system_named,
  sysfk.delete_referential_action,sysfk.delete_referential_action_desc,
  sysfk.update_referential_action,sysfk.update_referential_action_desc
  from sys.foreign_keys sysfk 
  left join sys.tables syst on sysfk.parent_object_id=syst.object_id
  left join sys.tables systr on sysfk.referenced_object_id=systr.object_id
  left join sys.columns sysc on sysc.object_id =sysfk.parent_object_id and sysfk.key_index_id=sysc.column_id
  where sysfk.parent_object_id in(object_id('class'),object_id('student'),object_id('book'),object_id('student_book'))
  

  输出：
  

最后一个，非空约束：
这个是概念上的约束，直接作用到列的属性上。在元数据中并没有体现出来“非空约束”这种类型。

二、表索引

2.1 表索引和表约束概述

索引分类：

索引只有两类：聚集索引和非聚集索引。聚集索引就是将索引和数据存放到一起，找到了索引也就找到了数据；非聚集索引则是将索引和数据分开存放，查询时先找到索引然后根据索引找到数据。聚集索引的效率高于非聚集索引。

索引是否唯一：

无论是聚集索引或非聚集索引都是可以唯一或不唯一的。从创建索引的语句上可以看得出来：

IF EXISTS(SELECT * FROM sys.tables WHERE name = N'test')
 DROP table test;
GO
create table test(
	a int not null,
	b int unique,
	c int,
	d int
)
go

--创建聚集索引
CREATE CLUSTERED INDEX IX_test_a ON test(a)
--创建非聚集索引
CREATE NONCLUSTERED INDEX IX_test_d ON test(d)  
--创建唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_test_a ON test(a)
--创建唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX IX_test_d ON test(d)  

再说一下主键约束和唯一约束：
从上面查询主键和唯一约束元数据的时候可以看的出来它们之间是有关系的。

• 对于某个列来说，最多只存在主键约束和唯一约束中的一种，主键约束的效果要比唯一约束严格。
• 在给表加主键约束的时候，sqlserver创建了主键约束并且创建了聚集唯一索引。
• 在给表加唯一约束的时候，sqlserver创建了唯一约束并且创建了非聚集唯一索引。

主键约束、唯一约束和索引如下图：

2.2 表索引约束关系实验以及查看索引元数据

创建一张表：

IF EXISTS(SELECT * FROM sys.tables WHERE name = N'test')
    DROP table test;
GO
create table test(
	a int not null,
	b int unique,
	c int,
	d int
)
go

此时查看，它的索引和约束情况：

-- 查看索引
sp_helpindex 'test';
go
-- 查看约束
sp_helpconstraint 'test';
go

输出如下：

可以看到，此时有一个唯一约束和一个同名的唯一索引，并且这个唯一索引是非聚集的。

注意：如果你将这个唯一约束，删掉的话，同名的索引也会被删除，你可以试一下:
alter table test drop constraint UQ__test__3BD0198EE0C026D9，我这里就不演示了。

然后，设置列a为主键，观察约束和索引情况：

ALTER TABLE test ADD CONSTRAINT PK_test_a PRIMARY KEY(a)
go
sp_helpindex 'test';
go
-- 查看约束
sp_helpconstraint 'test';
go

输出：

可以看到，设置主键后，sqlserver自动增加了一个主键约束以及主键索引（同名的）。

然后，继续创建一个唯一非聚集索引和一个不唯一非聚集索引，观察约束和索引情况：

create NONCLUSTERED INDEX IX_test_c_d ON test(c,d);
go
CREATE UNIQUE NONCLUSTERED INDEX IX_test_d ON test(d) 
go
sp_helpindex 'test';
go
-- 查看约束
sp_helpconstraint 'test';
go

输出：

可以看到，我们直接创建索引的话，sqlserver就直接给我们创建的索引，并没有创建约束。
最后，我们再添加一个唯一约束，观察约束和索引情况：

ALTER TABLE test ADD CONSTRAINT UQ_test_c UNIQUE (c)
go
sp_helpindex 'test';
go
-- 查看约束
sp_helpconstraint 'test';
go

输出：

总结上面的关系，查询索引的元数据如下（手动加的索引没找到创建和修改时间）：

select OBJECT_SCHEMA_NAME(sysi.object_id) as 'schema',sysf.name  as 'filegroup', sysi.name as '索引名称', syst.name as '表名',sysc.name as '列名',sysi.type_desc as '索引类型',
sysi.is_unique as '索引是否要求唯一',sysc.is_nullable as '索引是否允许为空',sysi.is_primary_key as '是否是主键约束',sysi.is_unique_constraint as '是否是唯一约束',
sysk.create_date,
sysk.modify_date
from sys.indexes sysi 
left join sys.key_constraints sysk on sysi.object_id=sysk.parent_object_id and sysi.name=sysk.name
left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
left join sys.tables syst on sysi.object_id=syst.object_id
left join sys.columns sysc on sysi.object_id=sysc.object_id and sysic.column_id=sysc.column_id
left join sys.filegroups sysf on sysi.data_space_id=sysf.data_space_id

where sysi.object_id=OBJECT_ID('test')
order by sysi.name

输出如下：

注意： 索引中存在多个列的话会存在多行数据。

三、表触发器

参考：《SQLServer触发器》
sqlserver的表触发器按照触发的时机分为：after(for)和instead of两种；按照触发的条件分为insert、update、和delete三种；
sqlserver的表触发器本身还具有是否生效的状态。

创建触发器：

IF EXISTS(SELECT * FROM sys.tables WHERE name = N'test')
    DROP table test;
GO
create table test(
	id int primary key,
	name varchar(50)
)
go

IF EXISTS(SELECT * FROM sys.triggers WHERE name = N'tri_test_after_insert')
     drop trigger tri_test_after_insert;
GO
create trigger tri_test_after_insert
on test
after insert
as
begin
	declare @count int
	select @count=count(1) from inserted
	print '插入的数据: '+ convert(varchar,@count)+'行'	
end
go

IF EXISTS(SELECT * FROM sys.triggers WHERE name = N'tri_test_after_update')
     drop trigger tri_test_after_update;
GO
create trigger tri_test_after_update
on test
after update
as
begin
	declare @count int
	select @count=count(1) from deleted
	print '更新的数据: '+ convert(varchar,@count)+'行'
end
go

IF EXISTS(SELECT * FROM sys.triggers WHERE name = N'tri_test_after_delete')
     drop trigger tri_test_after_delete;
GO
create trigger tri_test_after_delete
on test
after delete
as
begin
	declare @count int
	select @count =count(1) from deleted
	print '删除的数据: '+convert(varchar,@count)+'行'	
end
go

现在可以试验一下效果：

insert into test(id,name) values(1,'小明'),(2,'小花');
update test set name=name+'2' where id=1;
delete from test where id=2;
select * from test;

然后，在创建几个触发器：

IF EXISTS(SELECT * FROM sys.triggers WHERE name = N'tri_test_instead_insert')
     drop trigger tri_test_instead_insert;
GO
create trigger tri_test_instead_insert
on test
instead of insert
as
begin
	declare @count int;
	select @count=count(1) from inserted where name='小刚';
	if(@count>0) 
		begin
			RAISERROR('不允许小刚插入',16,1);
			return;
		end
	else
		begin
			insert into test select * from inserted
		end
end
go


IF EXISTS(SELECT * FROM sys.triggers WHERE name = N'tri_test_instead_delete')
     drop trigger tri_test_instead_delete;
GO
create trigger tri_test_instead_delete
on test
instead of delete
as
begin
	declare @count int;
	select @count=count(1) from deleted where name='小明';
	if(@count>0) 
		begin
			RAISERROR('不允许删除小明',16,1);
			return;
		end
	else
		begin
			delete from test where id in (select id from deleted)
		end
end
go

alter table test disable trigger tri_test_instead_delete;
go

新创建的这两个触发器不再实验了，直接看它们的元数据查询：

select syst.name as tablename, 
	systri.name as trigger_name,
	trigger_owner = user_name(ObjectProperty( systri.object_id, 'ownerid')),
	trigger_schema = schema_name(syso.schema_id),
	systri.create_date,
	systri.modify_date,
	systri.is_disabled,
	systri.is_not_for_replication,
	systri.is_instead_of_trigger,	
	isupdate = ObjectProperty( systri.object_id, 'ExecIsUpdateTrigger'),
	isdelete = ObjectProperty( systri.object_id, 'ExecIsDeleteTrigger'),
	isinsert = ObjectProperty( systri.object_id, 'ExecIsInsertTrigger'),
	isafter = ObjectProperty( systri.object_id, 'ExecIsAfterTrigger'),
	isinsteadof = ObjectProperty( systri.object_id, 'ExecIsInsteadOfTrigger'),
	createsql=OBJECT_DEFINITION(systri.object_id)	
from sys.triggers systri 
left join sys.tables syst on systri.parent_id=syst.object_id
left join sys.objects syso on systri.object_id=syso.object_id
where systri.parent_id=object_id('test')

输出如下：