本文详细介绍了数据库管理中参照完整性的设置，包括级联删除和更新，以及如何添加和删除约束。同时，展示了触发器的使用，如记录分数更改历史和确保教授工资不低于4000元的规则。内容涵盖ALTER TABLE语句、CREATE TABLE语句和触发器的创建及应用。
https://www.cnblogs.com/xijin-wu/p/5632775.html
https://www.cnblogs.com/liuqiyun/p/8603088.html
https://blog.csdn.net/weixin_46218781/article/details/105934703
首先把sno和cno改成bigint类型的 不然后面的实验做不下去
1
老师的代码：
但是在已经创建好sc表的情况下 如何再添加参照完整性呢
格式为：
alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）
alter table sc
--当学生表里的学生被删除或修改 成绩表也相应被删除或修改
--课程表如果课程被删除了 这个成绩表里对应学生的记录不应被删除应该拒绝删除 但是如果课程号改了这里也相应被改
add constraint sno1 foreign key(sno) references student(sno) on delete cascade on update cascade,
constraint cno1 foreign key(cno) references course(cno) on delete no action on update cascade
select* from information_schema.constraint_column_usage 可以查看所有的约束
如何删除约束呢
现在数据库里的数据：
现在来试试删除学生和课程的记录会怎么样
先来看看把李勇的学号改了会发什么什么变化
再看看把course表的cno改了 sc表会发生什么变化
现在课被删除了 那么sc表的学生成绩记录会被删除吗
执行失败了
看看学生信息被删除了会不会级联删除
可以看到sc表里面李勇的信息也没了
2
create table dept (
deptno varchar(20) primary key,
dname varchar(20) unique not null,
)
delete from dept --因为前面测试了数据 现在清空一下表
insert into dept
values ('001','haha')
select * from dept
delete from dept --因为前面测试了数据 现在清空一下表
insert into dept
values ('001','haha')
insert into dept
values ('002','haha') --部门名称和上面相同报错
select * from dept
不过为什么为空不报错
3
因为学生表以前也建过了 删掉重建太可惜了
所以也不按老师的语句写了
男女是可以正常添加进去的
这样就不行了
附代码
alter table student
drop checksex --前面做了测试 这里删掉
alter table student
add constraint checksex
check (ssex in ('男','女'))
insert into student
values ('20121122','小王子','haha',18, 'cs')
select * from Student
4
select * from sc
alter table sc
add constraint checkgrade
check (grade>=0 and grade<=100);
insert into sc
values(4,201215122,100)
select * from sc
可以看到100是可以插入进去的
再试试101 --发生冲突
改成90又能正常执行了
5
这个性别的约束已经写好了
没有找到姓名不能为空的写法 右键设计里面自己手动去掉勾吧 然后年龄要改成int类型
alter table student
add constraint checksno check (sno>90000 and sno < 99999),
constraint checkage check (sage<30)
报错了 解决方法：https://blog.csdn.net/weixin_46218781/article/details/105934703
右键新建约束
嗯。。。为啥能成功
懒得弄了 就这样吧
年龄的约束正常
6
alter table student
drop constraint checksex
然后再测试一下
7
ALTER TABLE Student
DROP checkage;
ALTER TABLE Student
ADD CONSTRAINT checkage CHECK(Sage < 40);
8
第一步
create table scu(
sno bigint primary key,
cno bigint ,
oldgrade int,
newgrade int
)
第二步
create trigger sct
on sc
after update
as
begin
if( select grade from inserted)>1.1*(select grade from deleted)
begin
Insert into scu
Values((select sno from deleted),(select cno from deleted),(select grade from deleted),(select grade from inserted))
end
end
第三步
update sc
set Grade=140 where cno=2 and sno =201215121 --添加到新表
update sc
set Grade=81 where cno=3 and sno=201215121 --不会添加到新表
9
第一步
create table teacher(
id varchar(10) primary key,
职位 varchar(20),
工资 int
)
--后面会对001进行修改
insert into teacher
values ('001','教授',1000)
第二步
create trigger Insert_OR_Update_Sal
on teacher
for update,insert
as
begin
update teacher
set 工资=4000 where (
(select 工资 from inserted)<4000 and (select 职位 from inserted)='教授')
end
第三步
update teacher
set 工资=1000 where teacher.id=001 --查询一下发现工资变成了4000
insert into teacher
values ('002','教授',1000) --变4000
insert into teacher
values ('003','讲师',1000)
insert into teacher
values ('004','教授',5000)
select * from teacher
10 老师代码
【实验过程】
操作1：显式说明参照完整性的违约处理示例
CREATE TABLE SC
( Sno CHAR(9) NOT NULL，
Cno CHAR(4) NOT NULL，
Grade SMALLINT,
PRIMARY KEY(Sno,Cno)，
FOREIGN KEY (Sno) REFERENCES Student(Sno)
ON DELETE CASCADE /*级联删除SC表中相应的元组*/
ON UPDATE CASCADE, /*级联更新SC表中相应的元组*/
FOREIGN KEY (Cno) REFERENCES Course(Cno)
ON DELETE NO ACTION
/*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/
ON UPDATE CASCADE
/*当更新course表中的cno时，级联更新SC表中相应的元组*/
);
操作2：建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码
CREATE TABLE DEPT
( Deptno NUMERIC(2),
Dname CHAR(9) UNIQUE NOT NULL，
/*要求Dname列值唯一, 并且不能取空值*/
Location CHAR(10),
PRIMARY KEY (Deptno)
);
操作3：Student表的Ssex只允许取“男”或“女”。
CREATE TABLE Student
( Sno CHAR(9) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK （Ssex IN （‘男’,’女’）），
/*性别属性Ssex只允许取'男'或'女' */
Sage SMALLINT,
Sdept CHAR(20)
);
操作4：SC表的Grade的值应该在0和100之间。
CREATE TABLE SC
( Sno CHAR(9) ,
Cno CHAR(4),
Grade SMALLINT CHECK (Grade>=0 AND Grade <=100)，/*Grade取值范围是0到100*/
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
操作5：建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。
CREATE TABLE Student
( Sno NUMERIC(6)
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAINT C2 NOT NULL,
Sage NUMERIC(3)
CONSTRAINT C3 CHECK (Sage < 30),
Ssex CHAR(2)
CONSTRAINT C4 CHECK (Ssex IN ( ‘男’,'女')),
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
操作6：去掉例操作5对 Student表中对性别的限制。
ALTER TABLE Student
DROP CONSTRAINT C4;
操作7：修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40
可以先删除原来的约束条件，再增加新的约束条件
ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999),
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK(Sage < 40);
操作8：当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：
SC_U（Sno,Cno,Oldgrade,Newgrade）， 其中Oldgrade是修改前的分数，Newgrade是修改后的分数。
CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
OLD row AS OldTuple,
NEW row AS NewTuple
FOR EACH ROW
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)
操作9：定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。
CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher /*触发事件是插入或更新操作*/
FOR EACH ROW /*行级触发器*/
BEGIN /*定义触发动作体，是PL/SQL过程块*/
IF (new.Job='教授') AND (new.Sal < 4000)
THEN new.Sal :=4000;
END IF;
END;
1584
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
