Mysql学习随笔--5

1.主键  【不能重复  不能为空】----  索引

①：一张表只能有一个主键

②：一个主键可以是多列

2.外键  【约束不重复  可以为空】

①：节省空间

②：加速查找

3.外键实例

create table t5 (
nid int(11) not null auto_increment,
pid int(11) not null,
num int(11),
primary key(nid,pid)
) engine=innodb default charset=utf8;

 

create table t6(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
CONSTRAINT fk_t5_t6  foreign key  (id1,id2)  REFERENCES  t1(nid,pid)   
)engine=innodb default charset=utf8;

4.唯一索引

create table t1(
id int ....,
num int,
xx int,
unique 唯一索引名称 (列名,列名),
constraint ....
)

①一对一：

create table userinfo1(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;

 

create table admin(
id int not null auto_increment primary key,
username varchar(64) not null,
password VARCHAR(64) not null,
user_id int not null,
unique uq_u1  (user_id),
CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;

②多对多

create table userinfo2(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;

create table host(
id int auto_increment primary key,
hostname char(64)
)engine=innodb default charset=utf8;

create table user2host(
id int auto_increment primary key,
userid int not null,
hostid int not null,
unique uq_user_host (userid,hostid),  联合索引
CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;