时间戳

首页 > 解决方案 > 插入/更新，忽略违反 FK 的行

sql-server - 插入/更新，忽略违反 FK 的行

问题描述

如果某人对具有另一个外键的表执行更新或插入，如果出现不存在的值，则会引发错误。是否有足够自动化的方法来忽略有故障的列并继续其他列？

我只能想到一个替代触发器，但这听起来很乱。

标签： sql-servertsqlforeign-keys

解决方案

您应该通过将源数据与引用的表连接起来来检查 INSERT 查询中的行。

例如，假设您在SourceData表中有以下数据，并且您希望将其插入AdventureWorks2019数据库的Sales.CurrencyRate表中：

CREATE TABLE SourceData (
    CurrencyRateDate datetime,
    FromCurrencyCode char(3),
    ToCurrencyCode char(3),
    PRIMARY KEY (CurrencyRateDate, FromCurrencyCode, ToCurrencyCode),
    Rate money NOT NULL
)

INSERT INTO SourceData (CurrencyRateDate, FromCurrencyCode, ToCurrencyCode, Rate) VALUES 
('20200429','EUR','RON',4.8425),
('20200429','EUR','ROL',48425),
('20200430','EUR','RON',4.8421),
('20200430','EUR','ROL',48421)

INSERT INTO Sales.CurrencyRate (CurrencyRateDate, FromCurrencyCode, ToCurrencyCode, AverageRate, EndOfDayRate)
SELECT sd.CurrencyRateDate, sd.FromCurrencyCode, sd.ToCurrencyCode, sd.Rate, sd.Rate
FROM SourceData sd

如果您只是运行上面提到的 INSERT 语句，您会收到一条错误消息"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CurrencyRate_Currency_ToCurrencyCode". The conflict occurred in database "AdventureWorks2019", table "Sales.Currency", column 'CurrencyCode'."，因为表中不存在货币“RON” Sales.Currency。

为避免此错误并仅在引用的表中插入具有相应行的数据，您只需为每个 FK 使用 JOIN，如下所示：

INSERT INTO Sales.CurrencyRate (CurrencyRateDate, FromCurrencyCode, ToCurrencyCode, AverageRate, EndOfDayRate)
SELECT sd.CurrencyRateDate, sd.FromCurrencyCode, sd.ToCurrencyCode, sd.Rate, sd.Rate
FROM SourceData sd
INNER JOIN Sales.Currency c1 ON c1.CurrencyCode=sd.FromCurrencyCode
INNER JOIN Sales.Currency c2 ON c2.CurrencyCode=sd.ToCurrencyCode

推荐阅读