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 外键操作

标签：