讲义5:数据的完整性

--上课内容：第三单元表的创建和管理         --第四节 维护数据的完整性-- 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 uq_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 uq_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 uq_eid2 unique(ename)          --验证：是否定义在多个列上         create table emp8 (eid int ,ename varchar(10) ,edeptid int)         alter table emp8 add constraint uq_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 uq_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 ck_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 ck_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 ck_eid2 check(eid<10)         sp_helpconstraint emp11        --验证：是否定义在多个列上         create table emp12 (eid int ,ename varchar(10) ,edeptid int)         alter table emp12 add constraint ck_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 ck_eid3         sp_helpconstraint emp12     -- 5. 当要导入大量数据而要忽略检查的时候         alter table emp11 nocheck constraint ck_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(deptid int not null,deptname char(10))         alter table emp15 add constraint fk_edeptid foreign key(edeptid) references dept(deptid)       -- 我们为表设置外键时提示出错,          alter table dept add constraint pk_deptid primary key (deptid)         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(3,'May',3)   --因为dept表里没有deptid=3的记录         select * from emp15         select * from dept        --验证：如何修改外键的值         update dept set deptid=3 where deptid=2 --因为在emp15里有edeptid=2的记录         --加级联更新、级联删除         alter table emp15 drop constraint fk_edeptid         alter table emp15 add constraint fk_edeptid foreign key(edeptid) references dept(deptid) on update cascade on delete cascade          update dept set deptid=3 where deptid=2           delete from dept where deptid=3          sp_helpconstraint emp15         sp_helpindex emp15     -- 3. 删除表的外键约束         alter table emp15 drop constraint fk_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--表的创建和管理、数据插入修改删除 复习        数字型 整型 int 浮点型 real float decimal(10,2) numeric(m,n)) --数据类型 字符型 char(n) varchar(n) text        日期 datetime --数据定义语言DDL                                primary key                                uniquecreate table table_name(field_name1 datetype [null/not null|constraint constraint_name  check       ])                                foreign key                                 default            add field_name1 datetype            alter column field_name1 datetype            drop column field_name1alter table table_name             add  constraint constraint_name   primary key|unique|check|foreign key|default            drop constraint constraint_namedrop table table_name --数据操纵语言DMLinsert into table_name(field_name...) values(field_value...) nullupdate table_name set field_name=field_value... where  逻辑表达式delete from table_name where  逻辑表达式select