实例4(数据完整性).sql

经典的sql资料

--上课内容：第三单元表的创建和管理 

        --第四节 维护数据的完整性


-- 3.4 数据完整性

-- 3.4.1 内容及实现方法

    -- 内容：实体完整性、区域完整性、引用完整性、自定义完整性

    -- 实现方法：约束、规则、默认


-- 3.4.2 约束

  -- 3.4.2.1 主键约束 PRIMARY KEY(保证实体完整性)

   -- 特点：（用企业管理器中的操作来进行验证）

     -- 1. 限制取值唯一 

     -- 2. 值不能为NULL 
 
     -- 3. 可以定义在多个列上，其中某一列可以重复

     -- 4. 创建主键的同时系统自动创建聚集唯一索引

     -- 5. image 和 text 类型的列不能创建主键

     -- 6. 一个表只能有一个主键约束

   -- 在查询分析器里用Transact SQL语句实现：

     -- 1. 查看表的约束、索引
         sp_helpconstraint 表名
         sp_helpindex 表名

     -- 2. 创建一个表后，为表创建主键约束
         create table emp1(eid int,ename varchar(10),edeptid int)
         alter table emp1 add constraint pk_eid primary key(eid) 
       -- 我们为表设置主键时提示出错
         alter table emp1 alter column eid int not null  
         sp_helpconstraint emp1
         sp_helpindex emp1
         sp_help emp1

     -- 3. 创建一个表同时创建主键约束
       --  没有给定约束名，系统自动给定约束名
         create table emp2 (eid int primary key clustered,ename varchar(10),edeptid int)
         sp_helpconstraint emp2
         sp_helpindex emp2

       --  给定约束名
         create table emp3 (eid int constraint pk_eid1 primary key clustered,ename varchar(10),edeptid int)
         sp_helpconstraint emp3
         sp_helpindex emp3

     -- 4. 验证表的主键约束
         insert into emp1 values(1,'May',1)
        --验证：主键是否可以有重复值
         insert into emp1 values(1,'Alice',1) 
        --验证：主键是否可以有空值
         insert into emp1 values(NULL,'Alice',1) 
         insert into emp1 values(2,'Alice',1)
        --验证：主键是否可以多个
         alter table emp1 add constraint pk_eid3 primary key(ename)  
        --验证：主键是否定义在多个列上
         create table emp4 (eid int not null,ename varchar(10) not null,edeptid int)
         alter table emp4 add constraint pk_eid3 primary key(eid,ename)
         sp_helpconstraint emp4
         sp_helpindex emp4
         insert into emp4 values(1,'May',1)
         insert into emp4 values(1,'Alice',1)


     -- 5. 删除表的主键约束
         alter table emp4 drop constraint pk_eid3
         sp_helpconstraint emp4

  -- 3.4.2.2 唯一约束 UNIQUE 

   -- 特点：（用企业管理器中的操作来进行验证）

     -- 1. 限制取值唯一 

     -- 2. 值可以为NULL,但不能多值为NULL  

     -- 3. 可以定义在多个列上，其中某一列可以重复

     -- 4. 创建唯一约束的同时系统自动创建唯一索引

     -- 5. 一个表可以有多个唯一约束

   -- 在查询分析器里用Transact SQL语句实现：

     -- 1. 创建一个表后，为表创建唯一约束
         create table emp5(eid int,ename varchar(10),edeptid int)
         alter table emp5 add constraint uk_eid unique(eid) 
         sp_helpconstraint emp5
         sp_helpindex emp5
         sp_help emp5

     -- 2. 创建一个表同时创建唯一约束
       --  没有给定约束名，系统自动给定约束名
         create table emp6 (eid int unique,ename varchar(10),edeptid int)
         sp_helpconstraint emp6
         sp_helpindex emp6

       --  给定约束名
         create table emp7 (eid int constraint uk_eid1 unique,ename varchar(10),edeptid int)
         sp_helpconstraint emp7
         sp_helpindex emp7

     -- 3. 验证表的唯一约束
         insert into emp7 values(1,'May',1)
        --验证：是否可以有重复值
         insert into emp7 values(1,'Alice',1) 
        --验证：是否可以有空值
         insert into emp7 values(NULL,'Alice',1) 
         insert into emp7 values(2,'Mike',1)
        --验证：是否可以多个唯一约束
         alter table emp7 add constraint uk_eid2 unique(ename)  
        --验证：是否定义在多个列上
         create table emp8 (eid int ,ename varchar(10) ,edeptid int)
         alter table emp8 add constraint uk_eid3 unique(eid,ename)
         sp_helpconstraint emp8
         sp_helpindex emp8
         insert into emp8 values(1,'May',1)
         insert into emp8 values(1,'Alice',1)
         select * from emp8

     -- 4. 删除表的唯一约束
         alter table emp8 drop constraint uk_eid3
         sp_helpconstraint emp8

  -- 3.4.2.3 检查约束 CHECK（保证区域完整性）
     -- 1. 创建一个表后，为表创建检查约束
         create table emp9(eid int,ename varchar(10),edeptid int)
         alter table emp9 add constraint chk_eid check(edeptid<100)
         sp_helpconstraint emp9
         sp_helpindex emp9
         sp_help emp9

     -- 2. 创建一个表同时创建检查约束
       --  没有给定约束名，系统自动给定约束名
         create table emp10 (eid int,ename varchar(10),edeptid int check(edeptid<100))
         sp_helpconstraint emp10
         sp_helpindex emp10

       --  给定约束名
         create table emp11 (eid int ,ename varchar(10),edeptid int constraint chk_eid1 check(edeptid<10))
         sp_helpconstraint emp11
         sp_helpindex emp11

     -- 3. 验证表的检查约束
         insert into emp11 values(1,'May',1)
        --验证：是否做检查
         insert into emp11 values(2,'Alice',11) 
         update emp11 set edeptid=11 where eid=2
        --验证：是否可以多个检查约束
         alter table emp11 add constraint chk_eid2 check(eid<10)
         sp_helpconstraint emp11
        --验证：是否定义在多个列上
         create table emp12 (eid int ,ename varchar(10) ,edeptid int)
         alter table emp12 add constraint chk_eid3 check(eid<10 and edeptid<10)
         sp_helpconstraint emp12
         sp_helpindex emp12
         insert into emp12 values(1,'May',1)
         insert into emp12 values(11,'Alice',1)
         insert into emp12 values(1,'Alice',11)
         update emp12 set edeptid=11 where eid=1
         select * from emp12

     -- 4. 删除表的检查约束
         alter table emp12 drop constraint chk_eid3
         sp_helpconstraint emp12

     -- 5. 当要导入大量数据而要忽略检查的时候
         alter table emp11 nocheck constraint chk_eid1
         sp_helpconstraint emp11
         --恢复检查约束
         alter table emp11 check constraint chk_eid1

  -- 3.4.2.4 外键约束 FOREIGN KEY （保证引用完整性）

   -- 特点：（用企业管理器中的操作来进行验证）

     -- 1. 外键与主键的数据类型和长度必须一致

     -- 2. 被参照表的被参照字段必须有主键或唯一约束

     -- 3. 必须使用references子句

     -- 4. 外键约束不能自动创建索引

     -- 5. 在临时表不能使用外键约束

   -- 在查询分析器里用Transact SQL语句实现：

     -- 1. 创建2个表后，为表创建外键约束
         create table emp15(eid int,ename varchar(10),edeptid int)
         create table dept(edeptid int not null,edeptname char(10))
         alter table emp15 add constraint con_edeptid foreign key(edeptid) references dept(edeptid)
       -- 我们为表设置外键时提示出错, 
         alter table dept add constraint pk_edeptid primary key (edeptid)
         sp_helpconstraint emp15
         sp_helpindex emp15
         sp_help emp15

     -- 2. 验证表的外键约束
         insert into dept values(1,'人事部')
         insert into dept values(2,'财务部')

         insert into emp15 values(1,'May',1)
         insert into emp15 values(2,'Alice',2)
         insert into emp15 values(1,'May',3)   --因为dept表里没有edeptid=3的记录

         select * from emp15
         select * from dept

        --验证：如何修改外键的值
         update dept set edeptid=3 where edeptid=2 --因为在emp15里有edeptid=2的记录
         --加级联更新、级联删除
         alter table emp15 drop constraint con_edeptid
         alter table emp15 add constraint con_edeptid foreign key(edeptid) references dept(edeptid) on update cascade on delete cascade 

         update dept set edeptid=3 where edeptid=2  

         sp_helpconstraint emp15
         sp_helpindex emp15


     -- 3. 删除表的外键约束
         alter table emp15 drop constraint con_edeptid
         sp_helpconstraint emp15



  -- 3.4.2.4 默认约束 DEFAULT （为insert数据时给默认值）

     -- 1. 创建一个表后，为表创建默认约束
         create table emp13(eid int,ename varchar(10),edeptid int)
         alter table emp13 add constraint df_eid default 1 for edeptid
         sp_helpconstraint emp13
         sp_helpindex emp13
         sp_help emp13

     -- 2. 创建一个表同时创建默认约束         
         create table emp14 (eid int,ename varchar(10),edeptid int default(1))

     -- 3. 验证表的默认约束 
         insert into emp14(eid,ename) values(1,'aa')
         select * from emp14

     -- 4. 删除表的默认约束
         alter table emp13 drop constraint df_eid 
         sp_helpconstraint emp13


--练习：

-- 1. 创建班级表classes(c_id char(6),c_name char(10)) 学生表stu(s_id char(8),s_name char(10),s_gender char(2),c_id char(6))

-- 2. stu表设置s_id为主键,设置c_id和s_name为唯一约束，设置s_gender字段检查约束使输入的性别只能为'男'或'女'，并查看验证

-- 3. stu表设置c_id为外键，参照classes表的c_id字段,并验证

-- 4. stu表设置c_id为外键，参照classes表的c_id字段，建立级联更新约束，并验证








--练习参考答案：

-- 1. 创建班级表classes(c_id char(6),c_name char(10)) 学生表stu(s_id char(8),s_name char(10),s_gender char(2),c_id char(6))
create table classes(c_id char(6) not null,c_name char(10))
create table stu(s_id char(8),s_name char(10),s_gender char(2),c_id char(6))
drop table  classes

-- 2. stu表设置s_id为主键,设置c_id和s_name为唯一约束，设置s_gender字段检查约束使输入的性别只能为'男'或'女'，并查看验证
alter table stu alter column s_id char(6) not null
alter table stu add constraint pk_sid primary key (s_id)
alter table stu add constraint uk_s unique (s_id,s_name)
alter table stu add constraint chk_gender check(s_gender in ('男','女'))

sp_helpconstraint stu
insert into stu (s_id,s_name,s_gender,c_id) values('1','aa','男','1')
insert into stu (s_id,s_name,s_gender,c_id) values('2','bb','男','1')
insert into stu (s_id,s_name,s_gender,c_id) values('3','bb','你','1')
select * from stu

-- 3. stu表设置c_id为外键，参照classes表的c_id字段,并验证
alter table classes add constraint pk_cid primary key(c_id)
alter table stu add constraint con_cid foreign key(c_id) references classes(c_id) 
delete from stu

-- 4. stu表设置c_id为外键，参照classes表的c_id字段，建立级联更新约束，并验证
alter table stu drop constraint con_cid 
alter table stu add constraint con_cid foreign key(c_id) references classes(c_id) on update cascade

sp_helpconstraint stu
select * from classes
select * from stu


insert into classes values('1','aa')
insert into classes values('2','bb')

insert into stu (s_id,s_name,s_gender,c_id) values('1','zz','男','1')
insert into stu (s_id,s_name,s_gender,c_id) values('2','yy','男','2')
insert into stu (s_id,s_name,s_gender,c_id) values('3','xx','女','1')
insert into stu (s_id,s_name,s_gender,c_id) values('4','ww','女','2')

update classes set c_id='3' where c_id='2'