数据库的参照完整性

mooc链接： 可以使用以下代码cmd的管理员身份下运行， 注释不影响结果， 直接复制查看即可；

-- 参照完整性 --  -- 学生表系参照系部表系  create table department    (   sdept varchar(20) primary key,   pic varchar(8),   address varchar(20)  );     insert into department(sdept,pic,address)values("计算机科学与技术","郑小东","05B423");     insert into department(sdept,pic,address)values("软件工程","王红梅","05B501");     insert into department(sdept,pic,address)values("网络工程","赵学民","05B421");     insert into department(sdept,pic,address)values("互联网工程","金秋春","05A509");     select * from department;       create table student1       (      sno char(6) primary key,      sname varchar(8),      sex char(2),

				sbirth date,

				sdept varchar(20),

				constraint fk_s1_01 foreign key(sdept)references department(sdept)

-- 这个就是department是参照表， 如果department中如果数据删除后,那么student1中置空 
				on delete set null
-- 如果department中如果数据改变后,那么student1中也改变
				on update cascade

				);

				

				insert into student1(sno,sname,ssex,sbirth,sdept)

				values("091001","郑航飞","男","1990-09-18","软件工程");

				insert into student1(sno,sname,ssex,sbirth,sdept)

				values("091002","李志勇","男","1990-09-18","网络工程");

				insert into student1(sno,sname,ssex,sbirth,sdept)

				values("091003","肖娜","女","1990-09-18","软件工程");

				insert into student1(sno,sname,ssex,sbirth,sdept)

				values("091004","谢菲菲","女","1990-09-18","互联网工程");

				

				delete from department where sdept="互联网工程";

				update department set sdept="网络安全" where sdept="网络工程";

				select * from  department;

				select * from student1;

				

-- 参照关系和被参照关系可以是同一个关系

create table course
(

			cno char(3) primary key,

			cname varchar(30),

			ccredit tinyint,

			cpno char(3),

			constraint fk_c_01 foreign key(cpno)references course(cno)

);

		

insert into course(cno,cname,ccredit,cpno)values("001","计算机基础",2,null);

insert into course(cno,cname,ccredit,cpno)values("002","c语言",4,"001");
  # 如果只执行第二条插入语句， 会报错因为你参照的数据不存在
	# Cannot add or update a child row: a foreign key constraint fails (`homework`.`course`, CONSTRAINT `fk_c_01` FOREIGN KEY (`cpno`) REFERENCES `course` (`cno`))


select * from course;

		

		
-- 
-- 一个表参考多个表, 这个类型和大小都应该和参照表一致
-- sc表是选课表
create table sc
(
  sno char(6),
  cno char(3),
  grade tinyint
-- 下面两行的效果和alter table sc add constraint fk_sc_01 foreign key (sno) references student(sno);一样，只不过会在插入数据中有影响
-- add constraint fk_sc_01 foreign key (sno) references student(sno);
-- constraint fk_sc_02 foreign key (cno) references course(cno);

);

  

select * from student1;

select * from course;

select * from sc;

insert into sc(sno,cno,grade)values("091001","001",87);

insert into sc(sno,cno,grade)values("091001","002",88);

alter table sc add constraint fk_sc_01 foreign key (sno) references student(sno);

alter table sc add constraint fk_sc_02 foreign key (cno) references course(cno);
select * from sc;


删除参照完整性

alter table student1 drop foreign key fk_s1_01;

-- course表中
delete from course where cno = "001";
#这个是删除某一条数据 如果多个条有都有这个数据那么删除所有条

update course set cname = '计算机基础' where cno = "001"
#如何修改course表， 中刚才那个C语言中的先修课应该是计算机基础 cno是001；