create table advisor(
s_ID varchar (5),
i_ID varchar (5),
primary key (s_ID),
foreign key (i_ID) references instructor (ID)
on delete set null,
foreign key (s_ID) references student (ID)
on delete cascade
);
create table prereq(
course_id varchar(8),
prereq_id varchar(8),
primary key (course_id, prereq_id),
foreign key (course_id) references course
on delete cascade,
foreign key (prereq_id) references course
);
foreign key
옵션
on delete set null
: foreign key(i_ID
)가 참조하는 instructor 테이블이 삭제되면 foreign key(i_ID
)를 null로 설정한다.on delete cascade
: foreign key(s_ID
)가 참조하는 student
테이블이 삭제되면 이 테이블(advisor
)도 삭제한다.prereq_id
)가 참조하는 course
테이블은 삭제될 수 없다.