数据库的完整性实验 | |||
注意:原版word在下载资源里面(免费下载) | |||
实验目的及要求: | |||
实验内容及步骤: 创建一个新的数据库stu,在数据库里建三个表分别是student、course和sc 创建数据库stu: create database stu on primary (name=stu, filename='D:\database\stu.mdf', size=10, maxsize=100, filegrowth=5) log on (name=stu_log, filename='D:\database\stu_log.ldf', size=10, maxsize=50, filegrowth=5) 建三个表 use stu create table student (sno char(9) not null, sname char(8), ssex char(2), sage int, sdept char(2)); create table course (cno char(4) not null, cname char(16), credit int); create table sc (sno char(9) not null, cno char(4)not null, grade int check(grade<=100 and grade>=0), ) 以系统管理员身份登录到SQL Server服务器, 1. 定义stu数据库中student表的主键sno; use stu alter table student add constraint sno_pk primary key(sno) --约束名为sno_pk 2. 将数据库stu的表course的cno字段定义为主键,约束名称为cno_pk; use stu alter table course add constraint cno_pk primary key(cno)--约束名为 cno_pk
alter table course add constraint uq_cname unique(cname)
alter table sc add constraint sc_pk primary key(sno,cno)
alter table sc add constraint sc_s_fk foreign key(sno) references student(sno) alter table sc add constraint sc_c_fk foreign key(cno) references course(cno) 实现如下参照完整性,设置成功后进行验证:
alter table sc add constraint sc_stu_fk foreign key(sno) references student(sno) on delete cascade
alter table sc add constraint fk_sno_student foreign key (sno) references student(sno) on delete cascade on update no action
alter table sc add constraint sc_c_fk foreign key(cno) references course(cno) on update cascade
alter table sc add constraint fk_cno_course foreign key (cno) references course(cno) on delete cascade on update cascade
alter table sc add constraint sc_f1 foreign key(sno) references student(sno) on update no action
(难) 方法一: Alter table student Add CONSTRAINT sno_ck 方法二: alter table student with nocheck add constraint sno_ck check ([sno] like '[1-9]00%s' and len(sno)=9)
alter table student with nocheck add constraint ck_age check (16<=sage and sage<=25)
(难) 方法一: Alter table student Add CONSTRAINT sname_ck 方法二: alter table student with nocheck add constraint ck_name check (len(Sname)<=8 and len(Sname)>=2)
alter table student with nocheck add constraint ck_sex check (Ssex in ('男','女'))
alter table student add constraint df_age default '20' for sage
alter table student drop ck_age alter table student with nocheck add constraint ck_age check (15<=Sage and Sage<=30)
alter table sc drop constraint fk_sno_student,fk_cno_course alter table course drop constraint uq_cname,sc_s_fk alter table student drop constraint sno_pk,sno_ck,cno_pk,sc_s_fk --删除的顺序,一般不能改变,因为约束之间存在羁绊,比如需要先删除course表中的sc_s_fk约束才能删除student的sc_s_fk的约束。唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的 |
数据库SQL Server实验报告 之 数据库的完整性实验(8/8)
于 2023-06-19 14:04:56 首次发布