MariaDB数据库的外键约束实例详解

MariaDB [market]> select * from user_order;   # 查看order表的数据
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
3 rows in set (0.00 sec)

2.1.3 测试级联更新

更新数据之前的状态

MariaDB [market]> select * from userprofile;  # 查看userprofile表的数据
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 2 | LB | 2 |
| 3 | HPC | 1 |
+----+------+-----+
3 rows in set (0.00 sec)

MariaDB [market]> select * from user_order;   # 查看order表的数据
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
3 rows in set (0.00 sec)

更新数据

MariaDB [market]> update userprofile set id=6 where id=2; # 把userprofile数据表中id为2的用户改为id为6
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

更新数据后的状态

MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 联表查询，可以看出表中已经没有id为2的用户了
+----+------+-----+-------+------+
| id | name | sex | money | o_id |
+----+------+-----+-------+------+
| 6 | LB | 2 | 146 | 2 |
| 3 | HPC | 1 | 256 | 3 |
+----+------+-----+-------+------+
2 rows in set (0.00 sec)

MariaDB [market]> select * from userprofile;  # 查看userprofile表的数据，id只剩下3和6
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | HPC | 1 |
| 6 | LB | 2 |
+----+------+-----+
2 rows in set (0.00 sec)

MariaDB [market]> select * from user_order;   # 查看user_order表的数据,u_id也改为6
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
2 rows in set (0.00 sec)

2.1.4 测试数据完整性

MariaDB [market]> insert into user_order(u_id,username,money)values(5,"XJ",345); # 单独向user_order数据表中插入数据，插入数据失败
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`market`.`user_order`, CONSTRAINT `user_order_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

在上面的例子中，user_order表的外键约束，user_order表受userprofile表的约束

在user_order里面插入一条数据u_id为5用户，在userprofile表里面根本没有，所以插入数据失败

先向userprofile表中插入记录，再向user_order表中插入记录就可以了

MariaDB [market]> insert into userprofile values(5,"XJ",1);  # 先向userprofile数据表中插入id为5的记录，插入数据成功
Query OK, 1 row affected (0.01 sec)

MariaDB [market]> insert into user_order(u_id,username,money) values(5,"XJ",345); # 再向user_order数据表中插入数据,成功
Query OK, 1 row affected (0.00 sec)

MariaDB [market]> select * from userprofile;  # 查询userprofile数据表中的所有记录
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | HPC | 1 |
| 5 | XJ | 1 |
| 6 | LB | 2 |
+----+------+-----+
3 rows in set (0.00 sec)

MariaDB [market]> select * from user_order;   # 查询user_order数据表中的所有记录
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
| 5 | 5 | XJ | 345 |
+------+------+----------+-------+
3 rows in set (0.01 sec)

2.2 方法二：通过alter table创建外键和级联更新，级联删除

语法：

alter table 数据表名称 add
 [constraint [约束名称] ] foreign key (外键字段,..) references 数据表(参照字段,...)
 [on update cascade|set null|no action]
 [on delete cascade|set null|no action]
)

例子：

MariaDB [market]> create table user_order1(o_id int(11) auto_increment,u_id int(11) default "0",username varchar(50),money int(11),primary key(o_id),index(u_id));  # 创建user_order1数据表，创建表时不使用外键约束
Query OK, 0 rows affected (0.11 sec)

MariaDB [market]> show create table user_order1;  # 查看user_order1数据表的创建信息，没有外键约束
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                     |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 | CREATE TABLE `user_order1` (
 `o_id` int(11) NOT NULL AUTO_INCREMENT,
 `u_id` int(11) DEFAULT '0',
 `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `money` int(11) DEFAULT NULL,
 PRIMARY KEY (`o_id`),
 KEY `u_id` (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!