SQL存储过程插入people等关联表外键约束报错如何解决？

问题根因 #

• 手动传入的@PeopleID参数无效：people表的id是IDENTITY自增字段，插入数据时由数据库自动生成值，你传入的固定值和实际生成的自增ID不匹配，导致外键关联失败
• people表插入值的顺序错误：原语句将phone和adress的赋值顺序写反，会导致字段数据存错
• 插入adress表时缺少必填字段：建表时adress表有非空字段numlocal，原插入语句只传入了地址和people_id，遗漏了@NumLocal参数
• 表名拼写错误：存储过程中写的插入目标表是phones，实际建表的表名为phone
• 原存储过程中@PeopleID属于冗余参数，不需要外部传入，应该取刚插入people表生成的自增ID

修正后的存储过程 #

Create Procedure newRegister
(
    @Name nvarchar(100),
    @Phone numeric(9),
    @Adress nvarchar(150),
    @NumLocal numeric(5),
    @Type_Fone nvarchar(20),
    @CodeArea numeric (3)
)
AS 
begin
    -- 插入people表，显式指定字段避免顺序错误
    INSERT INTO people (name, adress, phone) VALUES (@Name, @Adress, @Phone)
    -- 取当前作用域刚生成的people表自增主键ID
    DECLARE @GeneratedPeopleID INT = SCOPE_IDENTITY()
    -- 插入adress表，补齐numlocal必填字段
    INSERT INTO adress (adress, numlocal, people_id) VALUES (@Adress, @NumLocal, @GeneratedPeopleID)
    -- 插入phone表，修正表名
    INSERT INTO phone (phone, codearea, type_fone, people_id) VALUES (@Phone, @CodeArea, @Type_Fone, @GeneratedPeopleID)
end

正确调用语句 #

exec newRegister @Name = 'Victor', @Phone = 111222333, @Adress = 'Northen Street 55', @NumLocal = 155, @Type_Fone = 'Mobile', @CodeArea = 100

补充说明 #

SCOPE_IDENTITY()会返回当前会话当前作用域内最后生成的IDENTITY值，不会受其他用户插入操作的影响，是获取刚插入自增主键的安全方案。

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