mysql主键与外键

主键使用规则

• 表头值不允许重复，不允许赋NULL值
• 一个表中只能有一个primary key 表头
• 多个表头做主键，称为复合主键，必须一起创建和删除
• 主键标志PRI
• 主键通常与auto_increment连用
• 通常把表中唯一标识记录的表头设置为主键[行号表]

步骤一：练习主键的创建、查看、删除、添加、

验证主键

 语法格式1

create table 库.表(

表头名 数据类型 primary key ,

表头名 数据类型 ,

..... );

//建表

登录后复制

mysql>  create table db1.t35(
     -> name char(10) , 
     -> hz_id  char(10) primary key  , 
     -> class char(10) 
     -> );
 Query OK, 0 rows affected (0.49 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

//查看表头

登录后复制

mysql> desc db1.t35;
 +-------+----------+------+-----+---------+-------+
 | Field | Type     | Null | Key | Default | Extra |
 +-------+----------+------+-----+---------+-------+
 | name  | char(10) | YES  |     | NULL    |       |
 | hz_id | char(10) | NO   | PRI | NULL    |       |
 | class | char(10) | YES  |     | NULL    |       |
 +-------+----------+------+-----+---------+-------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

语法格式2

create table 库.表(

表头名 数据类型 ,

primary key(字段名)

);

//建表

登录后复制

mysql> create table db1.t36(
     -> name char(10) , 
     -> hz_id  char(10) , 
     -> class char(10),
     -> primary key(hz_id)  
     -> );
 Query OK, 0 rows affected (0.39 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

//查看表头

登录后复制

mysql> desc db1.t36;
 +-------+----------+------+-----+---------+-------+
 | Field | Type     | Null | Key | Default | Extra |
 +-------+----------+------+-----+---------+-------+
 | name  | char(10) | YES  |     | NULL    |       |
 | hz_id | char(10) | NO   | PRI | NULL    |       |
 | class | char(10) | YES  |     | NULL    |       |
 +-------+----------+------+-----+---------+-------+
 3 rows in set (0.00 sec

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

删除主键命令格式

alter table 库.表 drop primary key ;

//例子

登录后复制

mysql>  alter  table db1.t36  drop primary key ;
 Query OK, 0 rows affected (1.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0

• 1.
• 2.
• 3.

//查看表头

登录后复制

mysql> desc  db1.t36;
 +-------+----------+------+-----+---------+-------+
 | Field | Type     | Null | Key | Default | Extra |
 +-------+----------+------+-----+---------+-------+
 | name  | char(10) | YES  |     | NULL    |       |
 | hz_id | char(10) | NO   |     | NULL    |       |
 | class | char(10) | YES  |     | NULL    |       |
 +-------+----------+------+-----+---------+-------+
 3 rows in set (0.00 sec)
 mysql>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

添加主键命令格式

alter table 库.表 add primary key(表头名);

//例子

登录后复制

mysql> alter  table  db1.t36  add  primary key(hz_id);

• 1.

查看表头

登录后复制

mysql> desc db1.t36;
 +-------+----------+------+-----+---------+-------+
 | Field | Type     | Null | Key | Default | Extra |
 +-------+----------+------+-----+---------+-------+
 | name  | char(10) | YES  |     | NULL    |       |
 | hz_id | char(10) | NO   | PRI | NULL    |       |
 | class | char(10) | YES  |     | NULL    |       |
 +-------+----------+------+-----+---------+-------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

//使用t35表 验证主键约束

登录后复制

//插入第1条记录 正常
 mysql> insert into db1.t35  values ("bob","888","nsd2107");
 Query OK, 1 row affected (0.05 sec)
 
 //空不可以
 mysql> insert into db1.t35  values ("john",null,"nsd2107"); 
 ERROR 1048 (23000): Column 'hz_id' cannot be null
 mysql> 
 
 //与第1条重复不可以
 mysql> insert into db1.t35  values ("john","888","nsd2107"); 
 ERROR 1062 (23000): Duplicate entry '888' for key 'PRIMARY'
 
 //不重复也不是null可以
 mysql> insert into db1.t35  values ("john","988","nsd2107"); 
 Query OK, 1 row affected (0.07 sec)
 
 //查看表记录
 mysql> select  * from db1.t35 ;
 +------+-------+---------+
 | name | hz_id | class   |
 +------+-------+---------+
 | bob  | 888   | nsd2107 |
 | john | 988   | nsd2107 |
 +------+-------+---------+
 2 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.

步骤二：练习复合主键的使用

//创建复合主键 表头依次是客户端ip 、服务端口号、访问状态

登录后复制

mysql> create  table  db1.t39(
 cip   varchar(15) , 
 port  smallint ,  
 status  enum("deny","allow") , 
 primary key(cip,port)
 );

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

//插入记录验证

登录后复制

insert into  db1.t39  values ("1.1.1.1",22,"deny");
 insert into  db1.t39  values ("1.1.1.1",22,"deny"); 同时相同报错
 insert into  db1.t39  values ("1.1.1.1",80,"deny"); 可以
 insert into  db1.t39  values ("2.1.1.1",80,"allow");可以

• 1.
• 2.
• 3.
• 4.

//查看记录

登录后复制

mysql> select  * from db1.t39;
 +---------+------+--------+
 | cip     | port | status |
 +---------+------+--------+
 | 1.1.1.1 |   22 | deny   |
 | 1.1.1.1 |   80 | deny   |
 | 2.1.1.1 |   80 | allow  |
 +---------+------+--------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

//删除复合主键

登录后复制

mysql> alter table  db1.t39 drop primary key;
 Query OK, 3 rows affected (1.10 sec)
 Records: 3  Duplicates: 0  Warnings: 0

• 1.
• 2.
• 3.

//查看表头

登录后复制

mysql>  desc  db1.t39;
 +--------+----------------------+------+-----+---------+-------+
 | Field  | Type                 | Null | Key | Default | Extra |
 +--------+----------------------+------+-----+---------+-------+
 | cip    | varchar(15)          | NO   |     | NULL    |       |
 | port   | smallint             | NO   |     | NULL    |       |
 | status | enum('deny','allow') | YES  |     | NULL    |       |
 +--------+----------------------+------+-----+---------+-------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

//没有复合主键约束后 ，插入记录不受限制了

登录后复制

mysql>  insert into db1.t39 values("2.1.1.1",80,"allow");
 mysql> insert into db1.t39 values("2.1.1.1",80,"deny");

• 1.
• 2.

//查看表记录

登录后复制

mysql> select  * from  db1.t39;
 +---------+------+--------+
 | cip     | port | status |
 +---------+------+--------+
 | 1.1.1.1 |   22 | deny   |
 | 1.1.1.1 |   80 | deny   |
 | 2.1.1.1 |   80 | allow  |
 | 2.1.1.1 |   80 | allow  |
 | 2.1.1.1 |   80 | deny   |
 +---------+------+--------+
 5 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

//添加复合主键时 字段下的数据与主键约束冲突 不允许添加

登录后复制

mysql> alter table  db1.t39 add primary key(cip,port);
 ERROR 1062 (23000): Duplicate entry '2.1.1.1-80' for key 't39.PRIMARY'

• 1.
• 2.

//删除重复的数据

登录后复制

mysql> delete from db1.t39 where cip="2.1.1.1";
 Query OK, 3 rows affected (0.05 sec)
 mysql> select  * from  db1.t39;
 +---------+------+--------+
 | cip     | port | status |
 +---------+------+--------+
 | 1.1.1.1 |   22 | deny   |
 | 1.1.1.1 |   80 | deny   |
 +---------+------+--------+
 2 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

//添加复合主键

登录后复制

mysql> alter table  db1.t39 add primary key(cip,port);
 Query OK, 0 rows affected (0.67 sec)
 Records: 0  Duplicates: 0  Warnings: 0

• 1.
• 2.
• 3.

//查看表头

登录后复制

mysql>  desc  db1.t39;
 +--------+----------------------+------+-----+---------+-------+
 | Field  | Type                 | Null | Key | Default | Extra |
 +--------+----------------------+------+-----+---------+-------+
 | cip    | varchar(15)          | NO   | PRI | NULL    |       |
 | port   | smallint             | NO   | PRI | NULL    |       |
 | status | enum('deny','allow') | YES  |     | NULL    |       |
 +--------+----------------------+------+-----+---------+-------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

步骤三：练习与auto_increment连用的效果

表头设置了auto_increment属性后，

插入记录时，如果不给表头赋值表头通过自加1的计算结果赋值

要想让表头有自增长 表头必须有主键设置才可以

查看表结构时 在 Extra (额外设置) 位置显示

建表时 创建有auto_increment 属性的表头。实现的效果如下：

行号 姓名 班级 住址

1 bob nsd2107 bj

2 bob nsd2107 bj

3 bob nsd2107 bj

4 bob nsd2107 bj

//建表

登录后复制

mysql> create     table   db1.t38 (
     -> 行号   int   primary key  auto_increment   , 
     -> 姓名   char(10) , 
     -> 班级   char(7) , 
     -> 住址   char(10)
     -> );
 Query OK, 0 rows affected (0.76 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

//查看表头

登录后复制

mysql> desc db1.t38 ;
 +--------+----------+------+-----+---------+----------------+
 | Field  | Type     | Null | Key | Default | Extra          |
 +--------+----------+------+-----+---------+----------------+
 | 行号   | int      | NO   | PRI | NULL    | auto_increment |
 | 姓名   | char(10) | YES  |     | NULL    |                |
 | 班级   | char(7)  | YES  |     | NULL    |                |
 | 住址   | char(10) | YES  |     | NULL    |                |
 +--------+----------+------+-----+---------+----------------+
 4 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

//插入表记录 不给自增长表头赋值

登录后复制

mysql> insert  into  db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
 
 mysql> insert  into  db1.t38(姓名,班级,住址)values("bob","nsd2107","bj");
 
 mysql> insert  into  db1.t38(姓名,班级,住址)values("tom","nsd2107","bj");

• 1.
• 2.
• 3.
• 4.
• 5.

//查看表记录

登录后复制

mysql> select  * from db1.t38;
 +--------+--------+---------+--------+
 | 行号   | 姓名   | 班级    | 住址   |
 +--------+--------+---------+--------+
 |      1 | bob    | nsd2107 | bj     |
 |      2 | bob    | nsd2107 | bj     |
 |      3 | tom    | nsd2107 | bj     |
 +--------+--------+---------+--------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

自增长使用注意事项

//给自增长表头赋值

登录后复制

mysql> insert  into  db1.t38(行号,姓名,班级,住址)values(5,"lucy","nsd2107","bj");  
 Query OK, 1 row affected (0.26 sec)

• 1.
• 2.

//不赋值后 用最后1条件记录表头的值+1结果赋值

登录后复制

mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");  
 Query OK, 1 row affected (0.03 sec)

• 1.
• 2.

//查看记录

登录后复制

mysql> select  * from db1.t38 ;
 +--------+--------+---------+--------+
 | 行号   | 姓名   | 班级    | 住址   |
 +--------+--------+---------+--------+
 |      1 | bob    | nsd2107 | bj     |
 |      2 | bob    | nsd2107 | bj     |
 |      3 | tom    | nsd2107 | bj     |
 |      5 | lucy   | nsd2107 | bj     |
 |      6 | lucy   | nsd2107 | bj     |
 +--------+--------+---------+--------+
 5 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

//删除所有行

登录后复制

mysql> delete  from  db1.t38 ；

• 1.

//再添加行 继续行号 而不是从 1 开始

登录后复制

mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
 mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
 mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");

• 1.
• 2.
• 3.

//查看记录

登录后复制

mysql> select  * from db1.t38;
 +--------+--------+---------+--------+
 | 行号   | 姓名   | 班级    | 住址   |
 +--------+--------+---------+--------+
 |      8 | lucy   | nsd2107 | bj     |
 |      9 | lucy   | nsd2107 | bj     |
 |     10 | lucy   | nsd2107 | bj     |
 +--------+--------+---------+--------+
 3 rows in set (0.01 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

//truncate删除行 再添加行 从1开始

登录后复制

mysql> truncate table  db1.t38;  
 Query OK, 0 rows affected (2.66 sec)

• 1.
• 2.

//插入记录

登录后复制

mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");
 mysql> insert  into  db1.t38(姓名,班级,住址)values("lucy","nsd2107","bj");

• 1.
• 2.

//查看记录

登录后复制

mysql> select  * from db1.t38;
 +--------+--------+---------+--------+
 | 行号   | 姓名   | 班级    | 住址   |
 +--------+--------+---------+--------+
 |      1 | lucy   | nsd2107 | bj     |
 |      2 | lucy   | nsd2107 | bj     |
 +--------+--------+---------+--------+
 2 rows in set (0.01 sec)
 mysql>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

外键使用规则：

• 表存储引擎必须是innodb
• 表头数据类型要一致
• 被参照表头必须要是索引类型的一种(primary key)

作用：

• 插入记录时，表头值在另一个表的表头值范围内选择。

步骤一：练习外键的创建、查看、删除、添加

//创建外键命令

登录后复制

create table   库.表(
 表头列表 , 
 foreign key(表头名)        #指定外键
 references 库.表(表头名)   #指定参考的表头名
 on update  cascade         #同步更新
 on  delete  cascade        #同步删除
 )engine=innodb;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

需求： 仅给公司已有的员工发工资

创建员工表

表名 yg

员工编号 yg_id

姓名 name

登录后复制

create table  db1.yg (
 yg_id   int  primary key  auto_increment , 
 name  char(16) 
 ) engine=innodb;

• 1.
• 2.
• 3.
• 4.

创建工资表

表名 gz

员工编号 gz_id

工资 pay

登录后复制

mysql> create table db1.gz(
   gz_id int , pay  float,
 foreign key(gz_id)  references db1.yg(yg_id)
 on update cascade  on delete cascade
 )engine=innodb ;

• 1.
• 2.
• 3.
• 4.
• 5.

//查看工资表外键

登录后复制

mysql> show create  table db1.gz \G
 *************************** 1. row ***************************
        Table: gz
 Create Table: CREATE TABLE `gz` (
   `gz_id` int(11) DEFAULT NULL,
   `pay` float DEFAULT NULL,
   KEY `gz_id` (`gz_id`),
   CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

//删除外键

登录后复制

mysql> alter table db1.gz drop FOREIGN KEY  gz_ibfk_1;

• 1.

//查看不到外键

登录后复制

mysql> show create  table db1.gz \G
 *************************** 1. row ***************************
        Table: gz
 Create Table: CREATE TABLE `gz` (
   `gz_id` int(11) DEFAULT NULL,
   `pay` float DEFAULT NULL,
   KEY `gz_id` (`gz_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)
 mysql>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

//添加外键

登录后复制

mysql> alter table db1.gz 
 add  foreign key(gz_id)  references db1.yg(yg_id)  
 on update cascade  on delete cascade ;

• 1.
• 2.
• 3.

//查看外键

登录后复制

mysql> show create  table db1.gz \G
 *************************** 1. row ***************************
        Table: gz
 Create Table: CREATE TABLE `gz` (
   `gz_id` int(11) DEFAULT NULL,
   `pay` float DEFAULT NULL,
   KEY `gz_id` (`gz_id`),
   CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

步骤二：验证外键功能

1. 外键字段的值必须在参考表字段值范围内
2. 验证同步更新( on update cascade)
3. 验证同步删除( on delete cascade)

1)、外键字段的值必须在参考表字段值范围内

员工表插入记录

登录后复制

mysql> insert into db1.yg (name) values ("jerry"),("tom");
 mysql> select  * from db1.yg;

• 1.
• 2.

工资表插入记录

登录后复制

mysql> insert into db1.gz values(1,50000);
 mysql> insert into db1.gz values(2,60000);
 mysql> select  * from db1.gz;
 +-------+----------+
 | gz_id | pay      |
 +-------+----------+
 |     1 | 50000    |
 |     2 | 60000    |
 +-------+----------+
 2 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

没有的3号员工 工资表插入记录报错

登录后复制

mysql> insert into db1.gz values(3,50000);  
 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`gz`, CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)

• 1.
• 2.

员工表 插入编号3的员工

登录后复制

mysql> insert into db1.yg (name) values ("Lucy"); 
 mysql> select  * from  db1.yg;

• 1.
• 2.

可以给3号员工 发工资了

登录后复制

mysql> insert into db1.gz values(3,40000);

• 1.

2)、验证同步更新( on update cascade)

查看员工表记录

登录后复制

mysql> select * from db1.yg;
 +-------+-------+
 | yg_id | name  |
 +-------+-------+
 |     1 | jerry |
 |     2 | tom   |
 |     3 | lucy  |
 +-------+-------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

把yg表里编号是3的改成9

登录后复制

mysql> update db1.yg set yg_id=9 where yg_id=3;  
 mysql> select * from db1.yg;
 +-------+-------+
 | yg_id | name  |
 +-------+-------+
 |     1 | jerry |
 |     2 | tom   |
 |     9 | lucy  |
 +-------+-------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

工资表里编号是3的自动变成 9

登录后复制

mysql> select * from db1.gz;
 +-------+----------+
 | gz_id | pay      |
 +-------+----------+
 |     1 | 50000.00 |
 |     2 | 60000.00 |
 |     9 | 40000.00 |
 +-------+----------+
 3 rows in set (0.00 sec)
 
 mysql>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

3)、验证同步删除( on delete cascade)

删除前查看员工表记录

登录后复制

mysql> select * from db1.yg;  
 +-------+-------+
 | yg_id | name  |
 +-------+-------+
 |     1 | jerry |
 |     2 | tom   |
 |     9 | lucy  |
 +-------+-------+
 3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

删除编号2的员工

登录后复制

mysql> delete from db1.yg where yg_id=2; 
 Query OK, 1 row affected (0.04 sec)

• 1.
• 2.

删除后查看

登录后复制

mysql> select * from db1.yg; 
 +-------+-------+
 | yg_id | name  |
 +-------+-------+
 |     1 | jerry |
 |     9 | lucy  |
 +-------+-------+
 2 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.

查看工资表也没有编号2的工资了

登录后复制

mysql> select * from db1.gz;  
 +-------+----------+
 | gz_id | pay      |
 +-------+----------+
 |     1 | 50000     |
 |     9 | 40000     |
 +-------+----------+
 2 rows in set (0.00 sec)
 mysql>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

4)、外键使用注意事项

#被参考的表不能删除

登录后复制

mysql> drop table db1.yg;
 ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
 mysql>

• 1.
• 2.
• 3.

给gz表的gz_id表头 加主键标签

登录后复制

delete from db1.gz; # 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键
 alter table db1.gz add primary key(gz_id);  添加主键

• 1.
• 2.

保证每个员工只能发1遍工资 且有员工编号的员工才能发工资

登录后复制

mysql> insert into db1.gz values (1,53000);  报错
 mysql> insert into db1.gz values (9,58000);  报错
 mysql> insert into db1.gz values (NULL,80000); 报错

• 1.
• 2.
• 3.