多主键
最近在学习mysql的时候发现关于多主键的一个问题。
google中的一个结果得到说 主键 就是unique 和 not null的结合,但经过试验之后发现,在细节上,二者是有差异的。
假设这样一个情况:
一个用户表,用户ID和用户名都是唯一的。
也就是说,一个ID只能出现一次,用户名也是只能出现一次,那么这个时候可能的建表语句就是这样:
CREATE TABLE `user` (
`userid` SMALLINT UNSIGNED AUTO_INCREMENT,
`username` CHAR(16) NOT NULL,
`password` char(40) NOT NULL,
PRIMARY KEY (`userid`, `username`)
);
但是实际上,这样建出来的表不能实现我们的需求
插入以下数据将会是正常的
id | username |
---|---|
1 | mike |
2 | mike |
2 | alice |
数据中可以看到, 表中同时存在了2个同样的id和2个同样的username 这并不是我们想要的
这种主键称为 联合主键, 联合主键就是由1个以上的字段构成的唯一标识.
这种主键通常用在多对多的关系表中, 例如
一位老师可以教多门课程, 一门课程可以由多个老师来授课. 那么这种情况就可以考虑使用联合主键, 设置 PRIMARY KEY (course_id, teach_id) 这样就可以保证数据不会冗余。在这种情况下插入相同的(course_id, teach_id) 将会出错
course_id | teach_id |
---|---|
1 | 1 |
1 | 2 |
2 | 1 |
1 | 1 (错误) |
那么回到一开始的问题,要怎样确保userid和username都唯一?
建表语句如下
CREATE TABLE `user` (
`userid` SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`username` CHAR(16) NOT NULL,
`password` char(40) NOT NULL,
UNIQUE(`username`)
);
使用 unique语句来确保username的唯一性, 这样同时也保证了userid唯一性
小结: mysql中没有多主键,只有联合主键。也就是说不能够同时存在多个独立的primary key,但可以有多个unique.
外键
建立外键的前提: 本表的列必须与外键类型相同(外键必须是外表主键)。
外键作用: 使两张表形成关联,外键只能引用外表中的列的值!
指定主键关键字: foreign key(列名)
引用外键关键字: references <外键表名>(外键列名)
事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值),set Default(设默认值),[默认]no action
例如:
outTable表 主键 id 类型 int
创建含有外键的表:
create table temp(
id int,
name char(20),
foreign key(id) references outTable(id) on delete cascade on update cascade);
说明:把id列 设为外键 参照外表outTable的id列 当外键的值删除 本表中对应的列筛除 当外键的值改变 本表中对应的列值改变。