在关系型数据库设计中，父子表结构是常见的模式，其中父表通常包含一个自增的主键（primary key），而子表则包含一个外键（foreign key）引用父表的这个主键。当需要在同一个事务中同时向父表和子表插入数据时，会遇到一个挑战：在插入父表之前，其自增主主键的值是未知的；而在插入子表时，又必须知道这个主键值以建立正确的关联。本文将详细阐述如何在sql事务中优雅地解决这一问题。
解决方案：事务内获取自增主键
解决此问题的核心在于，数据库系统通常提供了一种机制，允许在INSERT语句执行之后、但在事务提交之前，获取该语句所生成的自增主键值。这样，我们就可以在同一个事务中先插入父表数据，立即获取其生成的ID，然后用这个ID去插入子表数据，从而保证整个操作的原子性和数据的一致性。
以下是针对不同主流数据库的实现方法：
1. SQL Server
在SQL Server中，推荐使用SCOPE_IDENTITY()函数来获取当前作用域（即当前批处理、存储过程、触发器或函数）中最后生成的标识值。
BEGIN TRANSACTION; -- 开启事务
-- 1. 插入父表数据
INSERT INTO ParentTable (Column1, Column2)
VALUES ('ParentValue1', 'ParentValue2');
-- 2. 获取刚刚插入的父表的自增主键ID
DECLARE @ParentID INT;
SET @ParentID = SCOPE_IDENTITY();
-- 3. 使用获取到的ID插入子表数据
INSERT INTO ChildTable (ParentID, ColumnA, ColumnB)
VALUES (@ParentID, 'ChildValueA', 'ChildValueB');
COMMIT TRANSACTION; -- 提交事务注意事项：
- SCOPE_IDENTITY()：返回当前作用域和当前会话中生成的最后一个标识值。它是最推荐的，因为它能避免由触发器或并发操作引起的混淆。
- @@IDENTITY：返回当前会话中生成的最后一个标识值，无论哪个作用域生成。如果父表上存在触发器，且触发器也插入了带有自增列的表，@@IDENTITY可能会返回触发器生成的ID，而非父表ID。
- IDENT_CURRENT('TableName')：返回指定表生成的最后一个标识值，不受作用域和会话限制。在高并发环境下不安全。
2. MySQL
在MySQL中，可以使用LAST_INSERT_ID()函数来获取当前会话中，上一个INSERT或UPDATE语句为AUTO_INCREMENT列生成的值。
START TRANSACTION; -- 开启事务
-- 1. 插入父表数据
INSERT INTO ParentTable (Column1, Column2)
VALUES ('ParentValue1', 'ParentValue2');
-- 2. 获取刚刚插入的父表的自增主键ID
SET @ParentID = LAST_INSERT_ID();
-- 3. 使用获取到的ID插入子表数据
INSERT INTO ChildTable (ParentID, ColumnA, ColumnB)
VALUES (@ParentID, 'ChildValueA', 'ChildValueB');
COMMIT; -- 提交事务注意事项：
- LAST_INSERT_ID()是会话（session）安全的，它返回的是当前客户端连接中最后一次生成的值，不会受到其他并发连接的影响。
3. PostgreSQL
PostgreSQL提供了多种方式，其中最现代和推荐的方式是使用RETURNING子句，它允许INSERT语句直接返回被插入行的列值。
BEGIN; -- 开启事务
-- 1. 插入父表数据并直接返回其ID，然后用CTE（公共表表达式）捕获
WITH inserted_parent AS (
INSERT INTO ParentTable (Column1, Column2)
VALUES ('ParentValue1', 'ParentValue2')
RETURNING id -- 假设自增主键列名为id
)
-- 2. 使用CTE中获取到的ID插入子表数据
INSERT INTO ChildTable (ParentID, ColumnA, ColumnB)
SELECT id, 'ChildValueA', 'ChildValueB'
FROM inserted_parent;
COMMIT; -- 提交事务如果不支持CTE或者希望分步执行（例如在应用程序代码中），也可以先插入父表，然后使用LASTVAL()或currval('sequence_name')来获取：
BEGIN;
INSERT INTO ParentTable (Column1, Column2)
VALUES ('ParentValue1', 'ParentValue2');
-- 获取刚刚插入的父表的自增主键ID
-- 假设id列由名为parent_table_id_seq的序列生成
-- SELECT currval('parent_table_id_seq');
-- 或者使用LASTVAL()，它返回当前会话中最近一次nextval的值
-- SELECT LASTVAL();
-- 实际应用中，通常会通过应用程序捕获RETURNING id的值，而不是再单独查询。
-- 例如，在Java的JDBC中，可以通过statement.getGeneratedKeys()获取。
-- 假设已获取到@ParentID
-- INSERT INTO ChildTable (ParentID, ColumnA, ColumnB)
-- VALUES (@ParentID, 'ChildValueA', 'ChildValueB');
COMMIT;注意事项：
- RETURNING子句是PostgreSQL中最优雅和高效的方法，尤其是在应用程序中捕获生成ID时。
- LASTVAL()返回当前会话中最近一次nextval()调用的值。
- currval('sequence_name')返回指定序列的当前值，但要求该序列在当前会话中已被nextval()调用过。
注意事项
- 事务的原子性： 所有涉及父子表的插入操作必须封装在同一个数据库事务中。这意味着要么所有操作都成功并提交，要么任何一步失败时所有操作都回滚，确保数据的一致性。
- 数据库兼容性： 获取自增主键的函数和语法因数据库系统而异。务必根据您使用的特定数据库选择正确的方法。
- 并发安全： 上述介绍的SCOPE_IDENTITY()和LAST_INSERT_ID()等函数都是会话（Session）安全的。它们返回的是当前会话中最后生成的ID，有效避免了多用户并发操作时可能出现的ID混淆问题。
- 错误处理： 在应用程序层面，应妥善处理数据库操作可能抛出的异常。一旦发生错误，应立即执行事务回滚操作，撤销所有已执行的插入，防止脏数据或不完整数据的产生。
总结
在SQL事务中插入父子表数据并处理自增主键关联是一个常见的需求。通过利用数据库提供的特定函数或语法（如SQL Server的SCOPE_IDENTITY()、MySQL的LAST_INSERT_ID()、PostgreSQL的RETURNING ID），我们可以在事务内部安全、高效地获取父表生成的自增主键，并将其用于子表的插入，从而确保数据完整性和事务的原子性。理解并正确应用这些机制是构建健壮数据库应用程序的关键。
