更新多个多对多关系

Question

我有两个表“Cars”和“Drivers”，它们与第三个表“CarDrivers”以多对多关系连接。

我的 UI 允许用户检查与当前汽车关联的任意数量的驾驶员。每项检查都表明应将一行输入 CarDrivers 表中。

我的问题是：当用户提交表单时更新这些行的最有效方法是什么？

我需要遍历并为每个已检查的项目在 CarDrivers 中添加一行，并为每个未检查的项目删除一行，同时保留那些未更改的项目。

我看到的唯一方法是一次一个地检查每个组合，然后添加那些不存在的组合或删除那些需要删除的组合。有更圆滑的方法吗？

我可以使用 Entity Frameworks 4、ADO.NET、直接 SQL 查询或存储过程。

Answer 1

这正是 MERGE 语法的情况被发明来处理，尤其是 WHEN NOT MATCHED BY SOURCE 子句。

一般来说，将当前状态值放入临时表中，然后使用 MERGE 一次处理 INSERT 和 DELETE 操作。

这是一个简短的草图：

CREATE TABLE Cars (VIN INTEGER NOT NULL UNIQUE);

CREATE TABLE Drivers (driver_licence_number INTEGER NOT NULL UNIQUE);

CREATE TABLE CarDrivers 
(
 VIN INTEGER NOT NULL REFERENCES Cars (VIN), 
 driver_licence_number INTEGER NOT NULL 
    REFERENCES Drivers (driver_licence_number)
);

INSERT INTO Cars VALUES (1), (2), (3);
INSERT INTO Drivers VALUES (22), (55), (99);

INSERT INTO CarDrivers VALUES (1, 22), (1, 55);

CREATE TABLE CarDrivers_staging
(
 VIN INTEGER NOT NULL REFERENCES Cars (VIN), 
 driver_licence_number INTEGER NOT NULL 
    REFERENCES Drivers (driver_licence_number)
);

INSERT INTO CarDrivers_staging 
   VALUES (1, 55), -- remains
          (1, 99); -- insert
                   -- DELETE (1, 22)

MERGE INTO CarDrivers
   USING CarDrivers_staging S 
      ON S.VIN = CarDrivers.VIN
         AND S.driver_licence_number = CarDrivers.driver_licence_number 
WHEN NOT MATCHED THEN
   INSERT (VIN, driver_licence_number)
      VALUES (VIN, driver_licence_number)
WHEN NOT MATCHED BY SOURCE THEN
   DELETE;

Answer 2

我怀疑这是否更有效，但也许更“灵活”，特别是如果您没有大量数据的话。当用户提交更新汽车与驾驶员关系的表单时，为什么不首先删除与他们相关的 CarDrivers 中的所有关系，然后只插入他们检查的关系呢？或者，您可以对 CarDrivers 中的两列都有一个 uniq 约束，然后只需担心插入和删除，而不用检查代码中的现有记录。