Day 5(11.8):(2)实现完整性

-- 4 ****************************************************
-- 实现完整性


/*
强制数据完整性可确保数据库中的数据质量。
例如，如果输入了 employee_id 值为 123 的职员，那么该数据库不应允许其他职员使用同一 ID 值。
如果计划将 employee_rating 列的值范围设定为从 1 到 5，则数据库不应接受 6。
如果表有一 dept_id 列，该列存储职员的部门编号，则数据库应只允许接受公司中的有效部门编号。


对表进行计划有两个重要步骤：标识列的有效值和确定如何强制列中的数据完整性。数据完整性有3种类型： 


域完整性
    域完整性是指给定列的输入有效性。
    强制域有效性的方法有：限制类型（通过数据类型）、格式（通过 CHECK 约束和规则）或可能值的范围
   （通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则）。


实体完整性
    实体完整性将行定义为特定表的唯一实体。
    实体完整性强制表的标识符列或主键的完整性（通过索引、UNIQUE 约束、PRIMARY KEY 约束或 IDENTITY 属性）。


引用完整性 （参照图 ‘引用完整性.bmp’）
    在输入或删除记录时，引用完整性保持表之间已定义的关系。
    引用完整性基于外键与主键之间或外键与唯一键之间的关系（通过 FOREIGN KEY 和 CHECK 约束）。
    引用完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值，
    如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。
    
    强制引用完整性时，SQL Server 禁止用户进行下列操作： 
        当主表中没有关联的记录时，将记录添加到相关表中。
        更改主表中的值并导致相关表中的记录孤立。
        从主表中删除记录，但仍存在与该记录匹配的相关记录。


方法：约束、规则等


约束：默认约束、主键约束、检查约束、唯一约束、外键约束


列约束：当约束定义在某个表的一列上时即为列约束
表约束：约束中包含了一个以上的列


*/
-- 4.1 --------------------------------------------------
-- DEFAULT
-- 对表中的字段提供默认值，作用于插入数据时省略该字段


-- 创建表的同时创建约束：
use Test
create table t_temp
(
name   varchar(20),
mobile varchar(20) constraint DF_t_temp_mobile default('unkown')
)
insert t_temp (name) values ('Tom Sawyer')
select * from t_temp
sp_helpconstraint t_temp


-- 实验：创建样例表，要求包含时间字段，该字段默认值为当前时间，并查询该约束




-- 修改表时创建约束：
create table t_temp_2
(
name   varchar(20),
mobile varchar(20)
)
alter table t_temp_2
add constraint DF_t_temp_2_mobile default('unkown') for mobile


insert t_temp_2 (name) values ('Tom Sawyer')
select * from t_temp_2
sp_helpconstraint t_temp_2




-- 实验: 把刚才的默认值改成 '58781234'




-- 4.2 --------------------------------------------------
-- CHECK
-- 限制输入符合约束的数据，作用于 insert ，update


use Test
create table T4
(
num  int,
name nchar(20),
age  int,
constraint CK_T4_age check (age>=18 and age<=55)
)


insert T4 values (1,'Angela Bennett',24)
select * from T4


-- 输入违反约束的纪录
insert T4 values (2,'Marie Fredriksson',17)


/* 
-- 如果在创建约束前，已经存在于约束冲突的纪录，怎么办？


sp_helpconstraint T4


alter table T4 
drop constraint CK_T4_age


insert T4 values (2,'Marie Fredriksson',17)
select * from T4


alter table T4
add constraint CK_T4_age check (age>=18 and age<=55)


-- 可以使对表中现有数据的约束检查实效  WITH NOCHECK


alter table T4
with nocheck
add constraint CK_T4_age check (age>=18 and age<=55)


select * from T4
sp_helpconstraint T4


-- 此时约束对新插入的数据仍然有效


insert T4 values (3,'Bill Clinton',56)


-- 如果要 update 旧的数据，也有效


select * from T4


update T4 set age=16 where num=2  -- 可以
update T4 set age=19 where num=2  -- 不可以
*/


-- 对新载入的数据，暂时使约束失效 NO CHECK
/*
原因：
    知道新数据不违反约束，可以使数据导入更快
    希望先导入数据，后检查违反约束的数据
*/
alter table T4
nocheck
constraint CK_T4_age 


insert T4 values (3,'Bill Clinton',56)
select * from T4


-- 检查表中与约束冲突的纪录


DBCC checkconstraints(CK_T4_age)


-- 实验： 可以对同一字段设置多个 CHECK 约束，对上述 age 字段增加第二个约束 (age>=25 and age<=60)，查看结果


-- 4.3 --------------------------------------------------
-- PRIMARY KEY
-- 强制表中记录的唯一性，作用于一个或多个字段
/*
每个表最多只能有一个 PK
PK 的字段必须唯一，不能为空
自动创建索引
*/


alter table T4
add constraint PK_T4_num primary key (num)


alter table T4
alter column num int not null


sp_helpconstraint T4
sp_helpindex T4


insert T4 values (3,'David R. Beckham',25)
select * from T4


-- 实验： 创建表 orders(oid int,pid int,quantity int) ，对 oid 和 pid 的组合建立主键并测试结果




-- 4.4 --------------------------------------------------
-- UNIQUE
-- 强制表中任意两条记录在约束的字段上不相同
/*
每个表可以创建多个 UNIQUE 约束
允许有一个空值
自动创建索引
*/


use Test
create table employees
(
name  varchar(20),
sid   varchar(15)
)
alter table employees
add constraint U_employees_sid unique (sid)


insert employees values ('Tom','310111122223333')
select * from employees


insert employees values ('Ann','310111122223333')


insert employees (name) values ('Ann')
select * from employees


insert employees (name) values ('Marie')


-- 4.5 --------------------------------------------------
-- FOREIGN KEY
/*
被引用的表的被引用字段必须是主键或唯一键
级联更新，级联删除 选项
也可以使约束实效
*/


use Test
create table employee
(
num   int,
name  varchar(20),
jobid int
)
create table job
(
jobid       int,
describtion varchar(50)
)


alter table employee
add constraint FK_emloyee_job foreign key (jobid) references job(jobid)


alter table job
add constraint U_job_jobid Unique (jobid)


insert employee values (1,'Tom',5)


insert job values (5,'CEO')


-- 级联
delete job where jobid=5


update job set jobid=4 where jobid=5


-- 原因：未启用级联更新，级联删除
alter table employee 
drop constraint FK_emloyee_job


alter table employee
add constraint FK_emloyee_job foreign key (jobid) references job(jobid)
on update cascade
on delete cascade 


alter table employee
add constraint FK_emloyee_job foreign key (jobid) references job(jobid)
on update set null
on delete set null


select * from job
select * from employee


update job set jobid=4 where jobid=5


delete job


-- 引用表中的外键可以为空
insert employee (num,name) values (1,'Tom')
select * from job
select * from employee


-- 对新导入的数据，使约束实效
insert employee values (2,'Ann',3)


alter table employee 
nocheck
constraint FK_emloyee_job


-- 在有违反约束的数据时，创建约束
alter table employee 
drop constraint FK_emloyee_job


alter table employee
with nocheck
add constraint FK_emloyee_job foreign key (jobid) references job(jobid)


默认
alter table 表名
add constraint 约束名 default 默认值 for 列名
主键
alter table 表名
add constraint 约束名 primary key(列名)
唯一
alter table 表名
add constraint 约束名 unique(列名)
检查
alter table 表名
add constraint 约束名 check(表达式)
外键
alter table 表名
add constraint 约束名 foreign key(外键字段) references 主键表(主键字段)










-- 实验： 创建表 orders(oid int,pid int,quantity int), products(pid int,pname varchar(50)) 
--        建立级联更新约束，并查看结果




-- 练习 --------------------------------------------------
-- 1 给以下电话簿增加约束，使得电话号码符合格式要求：'[phone]-408'
/*
create table contact(name varchar(20),phone char(17))
*/


-- 2 对刚才的建立的约束，使其对新载入的数据实效


-- 3 在企业管理器中实现引用完整性


-- 4 下表中，m_id 参照本表中的 e_id 字段，要求创建该约束，并将以下数据写入表中：
/*


use Test
Go
create table dbo.t_emp
(
e_id int,
name varchar(20),
m_id int
)


  -----------------------
  e_id  name         m_id
  -----------------------
  1207  Allen        1201
  1208  Jane         1201
  1209  Tom          1202
  1202  Marie        1201
  1201  Alex         null
  -----------------------


*/
-- 答案 --------------------------------------------------
-- 1