---老师表:用于记录在职的老师。
create table teacher
(
teacherID varchar(10),
teacherName varchar(10),
primary key (teacherID)
)---学生表:用于记录在校的学生。
create table student
(
studentID varchar(10),
studentName varchar(10),
primary key (studentID)
)---作业表:用于记录作业
create table homework
(
homeworkID varchar(10),
record varchar(100),
primary key (homeworkID)
)---老师——作业表:用于表示老师布置的作业
create table teacher_homework
(
teacherID varchar(10),
homeworkID varchar(10),
foreign key (teacherID) references teacher on update cascade
on delete cascade,
foreign key (homeworkID) references homework on update cascade
on delete cascade
)---学生——作业表:用于表示学生做了哪些作业
create table student_homework
(
studentID varchar(10),
homeworkID varchar(10),
foreign key (studentID) references student on update cascade
on delete cascade,
foreign key (homeworkID) references homework on update cascade
on delete cascade
)---有两个在职老师:张三、李四。
insert into teacher values('T001','张三')
insert into teacher values('T002','李四')---有三个在校学生:小李、小王、小刘
insert into student values('S001','小李')
insert into student values('S002','小王')
insert into student values('S003','小刘')---有三道作业:数学,物理,语文
insert into homework values('H001','语文')
insert into homework values('H002','物理')
insert into homework values('H003','数学')---其中老师张三布置了语文作业,老师李四布置了物理作业和数学作业
insert into teacher_homework values('T001','H001')
insert into teacher_homework values('T002','H002')
insert into teacher_homework values('T002','H003')---其中小李做了语文作业,小王做了数学作业,小刘做了物理作业
insert into student_homework values('S001','H001')
insert into student_homework values('S002','H003')
insert into student_homework values('S003','H002')---出于某个原因老师李四离职了
delete from teacher where teacherID='T002'---由于数据库的级联删除,把teacher_homework('T002','H002')、teacher_homework('T002','H003')也删除了。
---但是我们认为即使老师李四离职,他布置的数学、物理作业对于学生来说依然是有意义的,
---即不希望元组teacher_homework('T002','H002')、teacher_homework('T002','H003')被级联删除。---请问应该怎么解决这个问题??????
('T002','H002')('T002','H003'),这样写sql语句时麻烦了好多,如果teacher表参与多个联系表,sql语句
则更加麻烦。
在删除teacher表中的记录时保留元组('T002','H002')('T002','H003')