SQL SERVER实验指导课后习题

最新推荐文章于 2024-07-14 18:11:01 发布

银河xq

最新推荐文章于 2024-07-14 18:11:01 发布

阅读量284

点赞数

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/m0_62791984/article/details/128808259

版权

基于jiaoxuedb实验

1、对个表创建实体完整性；

use jiaoxuedb

alter table student
    add constraint pk_sno primary key(sno)

alter table teacher 
    add constraint pk_tno primary key(tno)

alter table course
    add constraint pk_cno primary key(cno)

alter table student
    add constraint unique_s unique(sno)

alter table teacher
    add constraint unique_t unique(tno)

alter table course
    add constraint unique_c unique(cno)

2、创建表之间的参照完整性

use jiaoxuedb
alter table sc with nocheck
    add constraint fk_s_sc foreign key(sno) references student(sno)
    on delete cascade

alter table sc with nocheck
    add constraint fk_c_sc foreign key(cno) references course(cno)
    on delete cascade

alter table tc with nocheck
    add constraint fk_t_tc foreign key(tno) references teacher(tno)
    on delete cascade

alter table tc with nocheck
    add constraint fk_c_tc foreign key(cno) references course(cno)
    on delete cascade

3、用不同的方法创建下面约束

“姓名”字段取唯一值：

use jiaoxuedb
alter table student
    add constraint unique_sna unique(sname)
alter table teacher
    add constraint unique_tna unique(tname)

b. “性别”字段取值为：‘男’或‘女’；默认值：‘男’；

use jiaoxuedb

alter table teacher with check
    add constraint check_t_sex check(sex='男' or sex='女')
alter table teacher
    add constraint df_t_sex default '男' for sex

alter table student with check
    add constraint check_s_sex check(sex='男' or sex='女')
alter table student
    add constraint df_s_sex default '男' for sex

c. “年龄”和“分数”字段取值限定为：0~100之间

use jiaoxuedb

alter table student with check
    add constraint check_s_age check(age>0 and age<100)
alter table teacher with check
    add constraint check_t_age check(age>0 and age<100)