插入/更新，忽略可能违反FK的行

Question

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

我只能想到一个而不是触发器，但这听起来很混乱。

Answer 1

您应该通过将源数据与引用的表连接来检查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