SQL Server中插入含自动生成外键的记录及存储过程实现

场景说明 #

POSTADRESSE表的AdresseID是IDENTITY自增主键，KUNDE表的AdresseID为引用该主键的外键。需要实现插入POSTADRESSE后，将自动生成的主键值关联插入KUNDE，且方案适配存储过程。

原实现的问题 #

你之前通过查询GateNavn获取AdresseID的方式存在严重缺陷：

• 若存在多条GateNavn相同的记录，查询会返回多个值，直接导致INSERT语句报错；
• 高并发场景下，可能获取到其他会话插入的ID，造成数据关联错误。

正确实现方案 #

方案1：使用SCOPE_IDENTITY()获取当前会话自增ID #

SCOPE_IDENTITY()返回当前会话、当前作用域中最后生成的IDENTITY值，是获取自增主键的标准方式。

-- 插入地址记录
INSERT INTO POSTADRESSE(GateNavn, GateNR, PostNR, PostSted)
VALUES ('Storgt', '3', 3901, 'Porsgrunn');

-- 用SCOPE_IDENTITY()获取刚生成的AdresseID，插入客户表
INSERT INTO KUNDE(TelefonNR, AdresseID, Epost, Fornavn, Etternavn, Passord) 
VALUES (
    '47843329',
    SCOPE_IDENTITY(),
    'hej@hotmail.se', 
    'Anton', 
    'Johanson', 
    '123abc'
);

注意：GateNR和TelefonNR均为varchar类型，插入时需传入字符串值（加单引号）。

方案2：使用OUTPUT子句捕获插入的主键值 #

如果需要在插入时直接将主键存入变量，可使用OUTPUT子句实现：

DECLARE @NewAdresseID INT;

-- 插入地址并将生成的AdresseID存入变量
INSERT INTO POSTADRESSE(GateNavn, GateNR, PostNR, PostSted)
OUTPUT INSERTED.AdresseID INTO @NewAdresseID
VALUES ('Storgt', '3', 3901, 'Porsgrunn');

-- 使用变量插入客户记录
INSERT INTO KUNDE(TelefonNR, AdresseID, Epost, Fornavn, Etternavn, Passord) 
VALUES (
    '47843329',
    @NewAdresseID,
    'hej@hotmail.se', 
    'Anton', 
    'Johanson', 
    '123abc'
);

封装为存储过程 #

将逻辑封装为存储过程，确保事务一致性，方便重复调用：

CREATE PROCEDURE InsertKundeWithAdresse
    @GateNavn VARCHAR(50),
    @GateNR VARCHAR(10),
    @PostNR INT,
    @PostSted VARCHAR(30),
    @TelefonNR VARCHAR(15),
    @Epost VARCHAR(320),
    @Fornavn VARCHAR(100),
    @Etternavn VARCHAR(100),
    @Passord VARCHAR(69)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    BEGIN TRY
        DECLARE @AdresseID INT;

        -- 插入地址记录
        INSERT INTO POSTADRESSE(GateNavn, GateNR, PostNR, PostSted)
        VALUES (@GateNavn, @GateNR, @PostNR, @PostSted);

        -- 获取自增主键
        SET @AdresseID = SCOPE_IDENTITY();

        -- 插入客户记录
        INSERT INTO KUNDE(TelefonNR, AdresseID, Epost, Fornavn, Etternavn, Passord)
        VALUES (@TelefonNR, @AdresseID, @Epost, @Fornavn, @Etternavn, @Passord);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 出错时回滚事务
        ROLLBACK TRANSACTION;
        -- 抛出错误信息
        THROW;
    END CATCH
END
GO

调用存储过程：

EXEC InsertKundeWithAdresse
    @GateNavn = 'Storgt',
    @GateNR = '3',
    @PostNR = 3901,
    @PostSted = 'Porsgrunn',
    @TelefonNR = '47843329',
    @Epost = 'hej@hotmail.se',
    @Fornavn = 'Anton',
    @Etternavn = 'Johanson',
    @Passord = '123abc';

内容的提问来源于stack exchange，提问作者VaBraAnton