mysql 外键

一、外键

constraint 名称  foreign key()  references 表名()

create table student(
    sid int auto_increment primary key,
    sname char(10),
    gender char(5),
    class_id int,
    constraint stu_key foreign key(class_id) references class(cid) 
    )engine=innodb default charset=utf8;

二、唯一索引

唯一索引和主键的区别

唯一索引：唯一但可以为空

主键：唯一且不为空

unique 名称 (多列)

联合唯一

三、外键变种

1、一对多

外键本身一对多

create table tb2(
    id int auto_increment primary key,
    name char(5)
)engine=innodb default charset=utf8;

create table tb1(
    id int auto_increment primary key,
    name char(4) not null,
    age int,
    addr varchar(10),
    tb2_id int,
    constraint fk_t foreign key(tb2_id) references tb2(id)
)engine=innodb default charset=utf8;

2、一对一

外键与唯一索引结合使用

create table tb2(
    id int auto_increment primary key,
    name char(5)
)engine=innodb default charset=utf8;

create table tb1(
    id int auto_increment primary key,
    name char(4) not null,
    age int,
    addr varchar(10),
    tb2_id int,
    unique u2(tb2_id),
    constraint fk_t foreign key(tb2_id) references tb2(id)
)engine=innodb default charset=utf8;

3、多对多(额外创建一张表)

多个外键和唯一索引

create table tb2(
    id int auto_increment primary key,
    name char(5)
)engine=innodb default charset=utf8;

create table tb3(
    id int auto_increment primary key,
    name char(5)
)engine=innodb default charset=utf8;

create table tb1(
    id int auto_increment primary key,
    name char(4) not null,
    age int,
    addr varchar(10),
    tb2_id int,
    tb3_id int,
    unique u2(tb2_id, tb3_id),
    constraint fk_t2 foreign key(tb2_id) references tb2(id),
    constraint fk_t3 foreign key(tb3_id) references tb3(id)
)engine=innodb default charset=utf8;