mysql 外鍵操作

標籤：

/*主鍵唯一的不可為空null,一個表的主鍵只能由一個，可以有一個主鍵一個唯一uk間*/
/*primary key(sname,sid) 複合主鍵可以同時控制sname，sid用的比較少*/
/*外鍵fk一般都是2個表，只支援innodb引擎*/
drop table teacher;
create table teacher(
tid smallint unsigned not null auto_increment primary key,
tname varchar(10)
)engine=innodb charset utf8;

create table student(
sid int unsigned not null auto_increment primary key,
sname varchar(10),
stid smallint unsigned,
constraint fk foreign key (stid) references teacher(tid)
)engine=innodb charset utf8;

create table student(
sid int unsigned not null auto_increment primary key,
sname varchar(10),
stid smallint unsigned,
constraint fk foreign key (stid) references teacher(tid) on delete set null on update set null/*此種刪除，只會刪除老師，讓學生的代課老師為空白*/
)engine=innodb charset utf8;

create table student(
sid int unsigned not null auto_increment primary key,
sname varchar(10),
stid smallint unsigned,
constraint fk foreign key (stid) references teacher(tid) on delete set null/*此種刪除，只會刪除老師，讓學生的代課老師為空白*/
)engine=innodb charset utf8;
drop table student;

create table student(
sid int unsigned not null auto_increment primary key,
sname varchar(10),
stid smallint unsigned,
constraint fk foreign key (stid) references teacher(tid) on delete cascade/*串聯刪除，刪除老師的同時，把學生也刪除了*/
)engine=innodb charset utf8;

insert into teacher values(null,‘李老師‘);
insert into teacher values(null,‘王老師‘);
insert into teacher values(null,‘陳老師‘);
insert into teacher values(null,‘名老師‘);

select * from teacher;

select database();
use xx;
insert into student values(null,‘李四‘,4);
insert into student values(null,‘小明‘,8);
insert into student values(null,‘李‘,7);
insert into student values(null,‘小‘,6);
select * from student;
/*刪除老師1，1老師有代課下面的學生表所以不能刪除，沒帶課的可以刪除*/
delete from teacher where tid=2;
delete from teacher where tid=7;
/*因為是學生表裡面有外鍵控制所以刪除的時候先刪除學生表再刪除老師表*/
drop table student;

mysql 外鍵操作