数据库完整性实验

create table student

 

(

 sno char(20) primary key,

 sname char(20) not null,

 ssex char(20),

 sage int,

 sdept char(20)

);

 

create table course(

 cno char(20) primary key,

 cname char(20) not null,

 cpno char(20),

 ccredit int ,

 foreign key(cpno) references course(cno)

 

);

 

 create table sc(

 sno char(20) not null,

 cno char(20) not null,

 grade int,

 

 primary key(sno,cno),

 foreign key(sno) references student(sno),

 foreign key(cno) references course(cno)

);

 

select * from student;

 

一、实体完整性

 

1.插入student表  当id一样时

insert into student values(2,'zhangsan','男',25,'cs');

 

insert into student values(2,'zhangsan','男',25,'cs');

insert into student(sname) values('zhangsan');

2.更新student表 将名字为lisi的sno修改为（已结有sno唯一的数据）

UPDATE student SET sno = 1 WHERE sname = 'lisi' ;

 

二、参照完整性

参照表SC  被参照表STUDENT

1.插入sc表

insert into sc values (1,2,60);

插入课程表

insert into  course values(1,'数据库原理',null,8);

满足条件

insert into sc values (1,1,60);

select * fromsc;

 

2.修改sc表中外码值

UPDATE sc SET sno = 9 WHERE grade = 60 ;

 

3.删除student表中的元组

delete student where sno=1;

 

4.修改student表中的主码值

UPDATE student SET sno = 9 WHERE sname = 'zhangsan' ;

 

三、用户定义的完整性

（1）不允许空值

insert into sc values (1,null,60);

 

（2）列值唯一

create table dept(deptno int,dname char(20) unique not null , loc char(20),primary key (deptno));

 

insert into dept values (1,'信息','包头青山区');

insert into dept values (2,'信息','包头昆区');

 

（3）用check短语指定值应该满足的条件

create table stu(sno char(9) primary key,sname char(20) not null , ssex char(2) check (ssex in('男','女')) ,sage int ,sdept char(20) );

 

insert into stu values ('1','zhangsan','无',25,'CS');

 

（4）元组上的约束条件

 

create table stu1(sno char(10),sname char(10) not null , ssex char(20) , sage int , sdept char(20), primary key (sno), check (ssex='女' or sname  not like 'Ms.%'));

 

insert into stu1 values('1','zhangsan','女',25,'CS');

 

insert into stu1 values('2','zhangsan','男',25,'CS');

 

insert into stu1 values('3','Ms.zhangsan','女',25,'CS');

insert into stu1 values('3','Ms.zhangsan','男',25,'CS');