SQL查询的艺术学习笔记--数据的完整性

use seldata
select * from sys.tables
drop table new_teacher
select * from course
--完整性约束
--完整性约束简称约束，是关系数据库中的对象，用来存放插入到一个表某一列数据的规则。
--数据完整性（对数据的准确性和一致性的保证）
--数据完整性：data integrity
--数据精确性：data accuracy
--数据可靠性：data reliability
--分为以下四类:
--实体完整性  域完整性  参照完整性  用户定义的完整性
--与表相关的约束： unique(唯一约束)   primary key(主键约束）
--                 foreign key(外键约束) check（检查约束）
--与列相关的约束：包括上述表约束 还增加了not null非空约束
--列约束和表约束的创建语法：
[constraint constraint_name]
constraint_type [column1,column2,....columnN]
--NOT null非空约束 表中所有列可以接受空值，但也可以添加非空约束
--非空约束只能用于列
--NOT null语法如下
column_name date_type|domain not null
use seldata
create table employeeinfo
(
emp_id      int not null,
emp_name    char(8) not null,
emp_address char(30) not null,
phone       char(11),
email       varchar(40)
)
select * from employeeinfo
--创建表，并添加非空约束
insert into employeeinfo
values (1,'wanghong','dongguan','01122334445','124ss@qq.com'),
       (2,'vivi','chongqing','32323211111',null)
select * from employeeinfo 
insert into employeeinfo
values (3,null,'shz','00000000000','2dfd@163.com')
--违反非空约束无法插入值    


--UNIQUE（唯一）约束
--语法：
column_name date_type|domain UNIQUE
create table uniqueemp
(
eid int not null,
ename char(10) not null,
eaddress varchar(30) not null,
phone char(11) unique,
email char(30) unique
)
alter table uniqueemp alter column
 eid char(6)
 --修改表字段类型
--创建非空约束和唯一约束表
insert into uniqueemp
values 
(001,'maomao','donan','11111111111','adds@163.com'),
(002,'xiaomi','dongqq','22222222222','bbd@263.com')
select * from uniqueemp
update uniqueemp
set eid='002' where ename='xiaomi'
insert into uniqueemp
values
('003','dudu','daxina','11111111111','adfdfd@111.com')
--测试违反unique约束
insert into uniqueemp
values
('003','dudu','daxina','11111111113','adfdfd@111.com')
update uniqueemp
set eid='abc'+eid
select * from uniqueemp
--通过连接字符为字段加上冠字头并批量更新
--unique以表约束添加 
--语法如下：
[constraint constraint_name]
unique (column1,column2,...columnN)
--这样可以以一个unique约束来控制多列数据
create table empunique
(
eid char(3) not null,
ename char(12) not null,
eaddress char(50) not null,
phone char(11),
email char(20)
constraint emp_unique unique (phone,email)
)
use seldata
--Primary Key (主键约束）
--主键约束的语法:
--定义单列时：
column_name  data_type|data_domain primary key
--定义多列为一个主键约束语法：
[constraint constraint_name]
primary key (column1,column2,.....columnN)
create table empinfo
(
eid int primary key,
ename char(12),
eaddress char(39),
phone char(11) unique,
email char(35) unique
)
--另一种写法：
create table empinfo1
(
eid int primary key,
ename char(13),
eaddress char(35),
phone char(11),
email char(35),
constraint peunique  unique (phone,email)
)
insert into empinfo
values
('001','daxia','xian','11112222333','abc@123.com'),
('002','xami','chonq','22222444333','qqq@abc.com')
drop table empinfo,empinfo1
select * from empinfo
insert into empinfo
values
('001','damaoq','ame','33234222233','sfd@ifeng.com')
--违反primary key 重复值约束测试
insert into empinfo
values
(null,'dadudu','anxing','3232323','abc@qq.com.cn')
--违反primary key null值插入失败测试
--整合多列primary key unique约束语句
create table empinfo2
(
eid int,
ename char(20),
eaddress char(60),
phone char(11),
email char(30),
constraint pidname primary key (eid,ename),
constraint uqphem  unique(phone,email)
)
--创建表后修改约束
--语法：
alter table table_naem
add constraint  constraint_name constrainttype|domain
(column1,column2,...columnN)
create table empinfo3
(
eid char(3),
ename char(17),
eaddress char(99),
phone char(11),
email char(22)
)
alter table empinfo3 
alter column eid char(3) not null
alter table empinfo3
alter column ename char(20) not null


--修改字段的非空约束
alter table empinfo3
add constraint pid primary key (eid,ename)


alter table empinfo3
add constraint Uam unique(phone,email)
--注意用上方法创建主键约束时要求约束键为非空约束


--外键约束Foreign KEY 可以为列约束，也可以为表约束
--外键约束是指某一列或一组列为外键
--子表：包含外部键的表
--父表：包含外部键引用的主键的表 父表取值为主键 或空值
--语法如下：
column_name data_type|domain references table_name (column)
/*column_name 子表添加foreign key列 
references 引用   table_name(父表) (column)父表所对应的列*/
[match full|partial|simples]
/*用于处理null值*/
[referential triggered action]
/*上面为可选字句：referential 参考  trigger 引发，触发*/
select * from empinfo
alter table empinfo
drop constraint PK__empinfo__D9509F6D1B0907CE 
--删除主键约束
--语法：
alter table table_naem
drop constraint PK_name(主键约束名)


alter table empinfo
alter column eid char(4) not null
--删除主键约束后再修改字段
insert into empinfo2
values
('003','xoxo','sd','2342424','abs@aa.com')
update empinfo
set eid='00'+eid
--批量修改字段
select * from empinfo
alter table empinfo  add constraint eifpk primary key(eid)
--修改表添加主键
create table emp_sal
(
empid char(4) references empinfo (eid),
salary decimal(5,1),
bankacc char(8)
)
--注意：外键字段和父表对应主键字段值类型应该一致
--另一种语句实现
create table emp_sal1
(
empid char(4),
salary decimal(5,1),
bakacc char(8),
constraint esfk foreign key(empid) references empinfo (eid)
--约束，约束名，约束类型，子表列 引用父表，父表列
)
--使用修改语句实现
alter table emp_sal1  drop constraint esfk
--删除前面所做的外键约束
alter table emp_sal1
add constraint esfk foreign key(empid) references empinfo (eid)
--修改添加外键约束完成
insert into emp_sal
values
('001',2111.3,'11121122'),
('002',3334.5,'23423444')
--‘001’,‘002’在父表中存在可以插入数据
insert into emp_sal
values
('008',3333.2,'2234324')
--'008'不存在于父键中插入失败


--检验约束（Check)
--Check约束可以定义为表约束，列约束，域约束 或者是断言
--check列约束创建语法：
column_name datatype|domain check (search condition)
--check 表约束创建语法：
constraint constraint_name check (search condition)
create table esal
(
emid int,
salary decimal(5,1),
bankacc char(8),
constraint ids check (emid in(1,2,3,4,5,6,7,8,9) and (salary>=1500 and salary <99999))
)
insert into esal
values
(1,2500,'222343'),
(3,9999,'fdfasd')
select * from esal
--测试检验之外数据插入
insert into esal
values
(99,2300,'sadfa')


--深入外键与完整性检查
--引用完整性检查包括以下四方面
--1.子表中的foreign key是父表的中primary key 列
--2.父表中的列被foreign key 引用需先删除子表中的foreign值再删除父表列
--3.若更新子表的foreign key 值与父表中的参考列中匹配值不存在，更新不会成功
--4.同样更新父表中的参考列若存在于子表foreign列中，同样更新不会成功。


--match（匹配）子句
--关于match 由于foreign key 约束的列中，带有null值是允许的。在多列foreign
--约束时，任意一列null值都会使foreign key约束检查失效。所以match的几种模式
--为解决这种情况有以下三种用法:
--1.match full :要求foreign key 约束中的所有列为null或者全不为nll
--2.match partial:允许foreign key 约束中的一列或多列为null，但不为null值列必须
--满足foreign key约束 所以称为：partial 部分
--3.match simple 这种用法等于不用match子句相同，即对有null值部分foreign key 值
--跳过检查。


--深入外键与完整性检查
--引用完整性检查包括以下四方面
--1.子表中的foreign key是父表的中primary key 列
--2.父表中的列被foreign key 引用需先删除子表中的foreign值再删除父表列
--3.若更新子表的foreign key 值与父表中的参考列中匹配值不存在，更新不会成功
--4.同样更新父表中的参考列若存在于子表foreign列中，同样更新不会成功。


--更新 删除操作规则:保持完整性的Foreign Key 与父表中的Primary Key
--匹配和参考值。而系统在更新或删除Parimary key行时会破坏引用的完整性。
--Foreign Key 提供了ON Update和ON delete子句按需要使用保证数据的完整性。
--Foreign Key完整语法：
column_name data_type|domain references table_name(cloumn)
[match fll|partial|simple]
[referential triggered action]
--其中referential triggered actionq包括了ON Update和ON Delete
--referential: 指示 参考的
--triggered:   引起，引发，触发
--action:      行为，活动
--RESTRICT:     限制，约束
--CASCADE:      串联，级联
--ON Update 语法
ON UPDATE
NO ACTION|CASCADE|RESTRICT|SET NULL|SET DEFAULT
--ON DELETE 语法
ON DELETE
NO ACTION|CASCADE||RESTRICT|SET NULL|SET DEFAULT


--完整的Foreign Key语法
--ON UPDATE
column_name  date_type|domain references table_name(column1,...columnN)
ON Update NO Action|cascade|Restrict|Set null|Set Default
--ON DELETE
column_name date_type|domain references table_name(column1,.....columnN)
ON DELETE NO ACTION|CASCADE|RESTRICT\SET NULL|SET DEFAULT
--NO ACTIION： 禁止更新或者删除父表中被引用的Primary Key，可临时允许
--CASCADE:联合操作，当父表删除/更新值时，子表对应值一起删除/更新。
--RESTRICT：等同于NO ACTION不能更新或删除父表中引用的Primary Key.不可临时允许。
--SET NULL：父表更新删除引用值后，子表相对应值设置NULL值。注意：子表是否、
--允许为NULL值，如不允许则更新失败？
--SET DEFAULT: 父表数据更新或删除后，子表中的数据被设置为默认值。注意：子表相应
--列是否有默认值：
use seldata
select * from sys.tables
create table emp
(
eid int not null,
bac char(4) not null,
ename char(20),
phone char(11)
)
--创建父表
create table emps
(
ssid int,
sbac char(4),
salary decimal(5,1),
)
--创建子表
select * from emp
select * from emps
insert into emp
values
(1,'1001','wqq','111'),
(2,'1002','1q11','23423'),
(3,'1003','3ssd','88888'),
(4,'1004','xa','8844'),
(5,'1005','mme','8343')
insert into emps
values
(1,'1001',3423.5),
(2,null,8888.8),
(null,1002,9999.9),
(3,null,777.3)
--添加数据
--添加外键约束并添加 on delete no action语句
alter table emps
add constraint em_fk foreign key (ssid,sbac)
references emp(eid,bac) on delete no action
--会提示你表主键不存在
--父表添加主键约束
alter table emp
add constraint empk primary key(eid,bac)


delete from emp where eid=1
--测试删除父表记录不成功
alter table emps
drop constraint em_fk
--删除外键约束
alter table emps
add constraint  em_fk foreign key(ssid,sbac)
references emp(eid,bac) on delete set default
--修改外键删除为cascade
delete from emp where eid=1
--测试删除父表数据成功
select * from emp where eid=2
select * from emps where ssid=1
delete from emps where salary=3423.5
--测试各模式下删除父表数据对应子表的变更


--域的约束与断言
--域约束和断言约束只能使用校检约束
--域约束是列中合法数据值约束 语法如下：
create domain domain_name as date_type 
--创建域  域名 域类型
[default default_value]
--可选指定默认值
[constraint constraint_name] chieck (value condition expression)
--创建约束 约束名 指定约束为域约束   值或表达式类型
create domain  void_no as int
constraint dmchi chieck (value between 100 and 999)
--(@_@) sql server不支持域约束
--实例：
create table tchi
(
vid void_no,--这里使用之前创建的域约束:void_no
vname varchar(20),
addr varchar(200),
phone char(11),
email varchar(202)
)
--这些代码在SQL Server下不受支持


--利用断言创建多表约束
--和域约束相比，断言是没有特定列多表检查约束，
--语法：
create assertion constraint_name
--创建断言 指定断言名
check serch condition
--检查约束  
create assertion asck
check (emp.eid in (select eid from emp_sal where ename is not null)


--SQL 完整性控制
--SQL数据完整性控制工具包话以下：
--1：规测    （RULE）
--2：缺省值  （Default)
--3: 触发器  （trigger)


--创建规则（rule)
--规则（rule）是对存储表的列或自定义数据值的规定和限制
--规则是独立于数据的数据库对象
--rule语法：
create rule rule_name as condition_expression
--创建规则 规则名 
--@condition_expression 子句定义了规则，可以是where 运算及关系
--范围，必须以@开头
use seldata
create rule salaryin
as @salary between 1500 and 5000
--类似申明一个变量？


--规则的绑定与松绑
--用存储过程sp_bindrule绑定规测
--语法如下：
sp_bindrule [@rulename=] 'rule',
--rule 是我们需要绑定规则的名称
[@objname=] 'object_name'
--obj_name 是绑定的对象
[,'futureonly']
--自绑定后自定义数据类型才会应用到这个新规则，之前数据不受影响
create rule bacrule
as @bac between 7500 and 10000
--创建规则
select * from emp
sp_bindrule  'bacrule','emp.bac'
--绑定规则
--注意:绑定规则对text image timestamp无效
--解除绑定规则语法：
sp_unbindrule [@objname=] 'object_name'
[,'futureonly']
--注意object_name为绑定对象名，而不是规则名
sp_unbindrule 'emp.bac'
--解除绑定
--注：2008后不再使用视图规则


--缺省值（default) 注：缺省值也是数据库对象
--缺省值对象（default）指当用户没有指定具体数据时，向相对应列中自动插入数据。
--缺省值用于多列或用户自定义数据类型
--表的列或自定义数据只能绑定一个缺省值、
--缺省值创建语法：
create default default_name as constant_expression
--constant 不变的，恒定的
select * from emp
create default phonebt
as '00000000000'
--创建一个缺省对象phonebt，创建后需要绑定使用使对象作用于数据生效
--缺省值的绑定与松绑
--缺省值绑定语法：
sp_bindefault  [@defname=] 'default'
--缺省值对象名
[@objname=] 'object_name'
--缺省值作用对象名
[,'futureonly']
--在创建以后数据生效。不影响之前数据
sp_bindefault 'phonebt','emp.phone'
--将默认对象phonebt绑定至表emp的phone字段
select * from emp
insert into emp
values 
(7,'1008','qavraa')
--缺省值解除绑定
sp_unbindefault 'emp.phone'
--解除绑定emp表的phone列