MySQL中的主键，唯一键，外键

主键

什么是主键：唯一标识表中的一个或一组称为主键。

主键的特性：不能重复和不能为空，一个表只能有一个主键，自动增长肯定是主键。

主键常与自动增长配合使用，自动增长从1开始，每次递增1，这样值就不会有重复，适合用来生成唯一的id。

创建主键的方法(表名student)：

1.create table student(id int auto_increment primary key,name varchar(20));

2.create table student(id int auto_increment,name varchar(20),primary key(id));

3.(创建组合键)create table student(classname varchar(20),stuname varchar(20),primary key(classname,stuname));

在创建完表之后添加主键(student)：

alter table student add primary key(id);

使用alter 添加自动增长(student)：

alter table student modifty id int auto_increment primary key;

删除主键(表名student)：

alter table student drop primary key;

查看主键方式(表名student)：

desc student;(查看表的结构)

show create table student;

唯一键

唯一键特性：不能重复，可以为空，一个表可以有多个唯一键,unique 和unique key 实现效果一样

添加唯一键(表名student)：

1.create table student(id int auto_increment primary key,name varchar(20) unique);

2.create table student(id int primary key,name varchar(20),unique (name));

通过修改表进行设置唯一键(表名student,字段是name)：

1.alter table student add unique(name);

2.(组合键，name和unique设置为唯一键)alter table student add unique(name),add unique(addr);

删除唯一键(表名student)：

alter table student drop index name;

外键

什么是外键：从表中的公共字段

外键有三种操作：一.严格限制二.置空操作三.级联操作

三种键的特性：只有Innodb才支持外键，公共字段可以不同名但是类型一定要一样

1.严格模式：(1). 主表中没有的记录，从表不允许插入(2). 从表中有的记录，主表中不允许删除(3). 删除主表前，先删子表

2.置空操作：如果主表记录删除，或关联字段更新，则从表外键字段被设置为null

3.级联操作：如果主表记录删除，则从表记录也被删除。主表更新，从表外键字段也更新。

置空、级联操作中外键不能是从表的主键

创建表的时候添加外键：

创建主表： create table studentinfo(id int primary key,name varchar(20))engine=innodb;

创建从表：create table studentscore(sid int primary key,score tinyint,foreign key(sid) references studentinfo(id))engine=innodb;(没有指定外键名称，系统默认)

当主表没有的关联字段从表不能插入：

mysql> insert into studentscore values(1,99);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`studentscore`, CONSTRAINT `studentscore_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `studentinfo` (`id`))
mysql> insert into studentinfo values(1,'libai');
Query OK, 1 row affected (0.01 sec)

当主表插入成功之后从表可以插入对应的关联字段：

mysql> insert into studentinfo values(1,'libai');
Query OK, 1 row affected (0.01 sec)

mysql> insert into studentscore values(1,99);
Query OK, 1 row affected (0.00 sec)

查看从表外键的名称以便删除：

mysql> show create table studentscore;
| studentscore | CREATE TABLE `studentscore` (
  `sid` int(11) NOT NULL,
  `score` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  CONSTRAINT `studentscore_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `studentinfo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
查找到外键名称为：studentscore_ibfk_1；

通过名称删除外键：

alter table studentscore drop foreign key `studentscore_ibfk_1`;(注意！！！里面的是反引号不是单引号)

通过修改表的方式添加外键：alter table studentscore add foreign key(sid) references studentinfo(id)；

下面设置外键的触发限制为置空(主表删除或者更新某个字段，从表将把对应关联字段设置为null)

创建主表的名称为studentinfo1：

create table studentinfo1(id int auto_increment primary key,name varchar(20))engine=innodb;

创建从表的名称为studentscore1：

create table studentscore1(sid int,score tinyint)engine=innodb;

往从表中添加外键名称为sid_id：

alter table studentscore1 add constraint sid_id foreign key(sid) references studentinfo1(id) on delete set null on update set null;(此处从表的关联字段不要设置为主键，因为主键不能为空和重复)

往主表和从表插入数据：

insert into studentinfo1 values(1,'libai'),(2,'dupu'),(3,'baijuyi');

insert into studentscore1 values(1,70),(2,65),(3,50);

查看表的数据：

select * from studentinfo1;

+----+---------+
| id | name    |
+----+---------+
|  1 | libai   |
|  2 | dupu    |
|  3 | baijuyi |
+----+---------+

select * from studentscore1;

+------+-------+
| sid  | score |
+------+-------+
|    1 |    70 |
|    2 |    65 |
|    3 |    50 |
+------+-------+

删除主表的id为1一列数据：

delete from studentinfo1 where id=1;

查看从表：

+------+-------+
| sid  | score |
+------+-------+
| NULL |    70 |
|    2 |    65 |
|    3 |    50 |
+------+-------+

更新主表id=2的数据：

update studentinfo1 set id=4 where id=2;

查看从表：

+------+-------+
| sid  | score |
+------+-------+
| NULL |    70 |
| NULL |    65 |
|    3 |    50 |
+------+-------+

删除从表sid=3的数据：

delete from studentscore1 where sid=3;

查看主表：

+----+---------+
| id | name    |
+----+---------+
|  3 | baijuyi |
|  4 | dupu    |
+----+---------+

发现从表删除数据对主表没有任何影响

级联操作：

创建主表的名称为studentinfo2：

create table studentinfo2(id int auto_increment primary key,name varchar(20))engine=innodb;

创建从表的名称为studentscore2：

create table studentscore2(sid int,score tinyint)engine=innodb;

插入三条数据：

insert into studentinfo2 values(1,'libai'),(2,'dupu'),(3,'baijuyi');

insert into studentscore2 values(1,70),(2,65),(3,50);

显示如下：

+----+---------+
| id | name    |
+----+---------+
|  1 | libai   |
|  2 | dupu    |
|  3 | baijuyi |
+----+---------+

+------+-------+
| sid  | score |
+------+-------+
|    1 |    70 |
|    2 |    65 |
|    3 |    50 |
+------+-------+

设置置空外键：alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id) on delete set null on update set null;

再次设置默认外键：alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id);报错，需要把外键删除后在重新设置触发限制

删除名为form_name外键：alter table studentscore2 drop foreign key form_name;

添加触发限制为级联的外键：

alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id) on delete cascade on update cascade;

删除主表的id=1的记录：

delete from studentinfo2 where id=1;

查找从表： select * from studentscore2;
+------+-------+
| sid  | score |
+------+-------+
|    2 |    65 |
|    3 |    50 |
+------+-------+

发现从表关联字段那一条记录被删除

主表把id=2的记录中id改为id=5

update studentinfo2 set id=5 where id=2;

查看从表： select * from studentscore2;
+------+-------+
| sid  | score |
+------+-------+
|    5 |    65 |
|    3 |    50 |
+------+-------+

发现从表中的对应关联字段的值同步主表更新

置空可以和级联联合使用：

删除表studentscore2的外键：alter table studentscore2 drop foreign key form_name;

此时的主表和从表是：

select * from studentinfo2;

+----+---------+
| id | name    |
+----+---------+
|  3 | baijuyi |
|  5 | dupu    |
+----+---------+

select * from studentscore2;

+------+-------+
| sid  | score |
+------+-------+
|    5 |    65 |
|    3 |    50 |
+------+-------+

设置置空和级联外键：alter table studentscore2 add constraint form_name foreign key(sid) references studentinfo2(id) on delete set null on update cascade;

当更新其中主表id=5数据时候：

update studentinfo2 set id=6 where id=5;

select * from studentscore2;

+------+-------+
| sid  | score |
+------+-------+
|    6 |    65 |
|    3 |    50 |
+------+-------+

发现从表同步更新记录

当删除主表的一条数据时候：

delete from studentinfo2 where id=3;

select * from studentscore2;
+------+-------+
| sid  | score |
+------+-------+
|    6 |    65 |
| NULL |    50 |
+------+-------+
发现从表此时使用置空方式

有错的地方或者不足的指出来大家讨论和大家一起进步