mysql主键与外键

2024-12-08 19:39:52

主键使用规则

表头值不允许重复，不允许赋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)

//查看表头

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)

语法格式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)

//查看表头

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

删除主键命令格式

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

//查看表头

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>

添加主键命令格式

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

//例子

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

查看表头

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)

//使用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)

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

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

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

//插入记录验证

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");可以

//查看记录

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)

//删除复合主键

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

//查看表头

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)

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

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

//查看表记录

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)

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

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

//删除重复的数据

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)

//添加复合主键

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

//查看表头

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)

步骤三：练习与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)

//查看表头

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)

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

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");

//查看表记录

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

自增长使用注意事项

//给自增长表头赋值

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

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

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

//查看记录

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)

//删除所有行

mysql> delete  from  db1.t38 ；

//再添加行继续行号而不是从 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");

//查看记录

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

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

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

//插入记录

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

//查看记录

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

外键使用规则：

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

作用：

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

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

//创建外键命令

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

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

创建员工表

表名 yg

员工编号 yg_id

姓名 name

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

创建工资表

表名 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 ;

//查看工资表外键

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

//删除外键

mysql> alter table db1.gz drop FOREIGN KEY  gz_ibfk_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>

//添加外键

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

//查看外键

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)

步骤二：验证外键功能

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

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

员工表插入记录

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

工资表插入记录

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)

没有的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)

员工表插入编号3的员工

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

可以给3号员工发工资了

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

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)

把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)

工资表里编号是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>

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)

删除编号2的员工

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

删除后查看

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

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

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

4)、外键使用注意事项

#被参考的表不能删除

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

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

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

保证每个员工只能发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

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

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

外键使用规则：

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

步骤二：验证外键功能

相关文章