sql-----字段约束----外键

最新推荐文章于 2024-02-20 10:41:32 发布

吉730

最新推荐文章于 2024-02-20 10:41:32 发布

阅读量137

点赞数

文章标签： sql mysql bash

本文链接：https://blog.csdn.net/weixin_69899223/article/details/125677210

版权

本文详细介绍了MySQL中外键的概念、作用以及使用规则，包括如何创建、查看、删除和添加外键。外键主要用于保证数据一致性，通过设置外键约束，实现表之间的关联并确保插入和更新操作的合法性。同时，文中通过实例演示了外键的同步更新和同步删除功能，以及在实际应用中外键的注意事项，如保证每个员工只能发一次工资等。

摘要由CSDN通过智能技术生成


外键
核心思想：保证数据一致性	
插入记录时，表头值在另一张表 表头值范围内选择。

外键的使用规则：
表存储引擎必须是innodb （在进阶课程里讲  现在仅需要知道如何指定表使用innodb存储引擎）
字段类型要一致
被参照字段必须要是索引类型的一种(通常是 primary key)

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

需求： 仅给公司里已经入职的员工发工资

首先创建存储员工信息的员工表
员工表  yg
员工编号   姓名
yg_id     name  
  1       bob
  2       bob

#创建员工表
create table  db1.yg (
yg_id   int  primary key  auto_increment , 
name  char(16) 
) engine=innodb;

#查看表的存储引擎 
show  create table  db1.yg \G

#没有表记录
select   *  from    db1.yg;   

创建工资表
工资表  gz
员工编号  工资
gz_id    pay

#创建工资表 指定外键表头
mysql> create table db1.gz(
    -> gz_id int , pay  float(7,2),##7位数，2个小数位，
    -> foreign key(gz_id)  references db1.yg(yg_id)
    -> on update cascade  on delete cascade
    -> )engine=innodb ;

通过查看表结构查看不到外键的设置   MUL是 普通索引的标志
mysql> desc  db1.gz;  
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| gz_id | int(11)    | YES  | MUL | NULL    |       |
| pay   | float(7,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

#通过显示建表命令查看外键 ，并获取外键名
Mysql> show  create  table 库名.表名 \G

查看工资表外键
mysql> show create  table db1.gz \G
*************************** 1. row ***************************
       Table: gz
Create Table: CREATE TABLE `gz` (
  `gz_id` int(11) DEFAULT NULL,
  `pay` float(7,2) 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  库.表  drop foreign  key    外键名;
例子
删除工资表的外键
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(7,2) DEFAULT NULL,
  KEY `gz_id` (`gz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 

添加外键命令
mysql> alter table 库.表 add  foreign key(表头名)  references 库.表(表头名)
on update cascade  on delete cascade;

例子
工资表添加外键
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(7,2) 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   验证同步更新（ on update cascade)
		3   验证同步删除（ 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,50000);
mysql> select  * from db1.gz;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 50000.00 |
|     2 | 50000.00 |
+-------+----------+
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,50000);	
		
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 | 50000.00 |
|     9 | 50000.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.00 |
|     9 | 50000.00 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> 
外键使用注意事项
#被参考的表不能删除
mysql> drop table db1.yg;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> 



给gz表的gz_id表头 加主键标签
保证每个员工只能发1遍工资  且有员工编号的员工才能发工资

# 如果重复发工资和没有编号的发了工资 删除记录后 再添加主键 
delete form db1.gz;
alter table db2.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); 报错