SQL：可选外键

Question

我有以下表格...

“楼层”（有很多）“区域”（有很多）“资产”，

然后我有一个“任务”表。

“任务”可以分配给“区域”或“资产”；所以我在“任务”表中有两列，名为areaId和assetId，其中一列必须有一个值。

第一个问题：如何查询某个楼层发生的所有任务？

第二个问题：如何强制引用完整性？

第三个问题：这种方法值得推荐吗？如果没有的话欢迎任何建议。

非常感谢

ETFairfax提供的任何答案

Answer 1

第一个问题：

SELECT
    distinct t.TaskID
FROM
    Floor f
        inner join
    Area a
        on
            f.FloorID = a.FloorID
        left join
    Asset ast
        on
            a.AreaID = ast.AreaID
        inner join
    Task t
        on
            t.AreaID = a.AreaID or
            t.AssetID = ast.AssetID
 WHERE
     f.FloorID = @FloorID

应该告诉您您正在尝试构建的查询的形状。 （当然，如果你已经有了FloorID，你实际上不需要查询Floor表）

第二个问题：

 CREATE TABLE Task (
     TaskID int not null,
     AreaID int null,
     AssetID int null,
     constraint PK_Task PRIMARY KEY (TaskID),
     constraint FK_Task_Area FOREIGN KEY (AreaID) references Area (AreaID),
     constraint FK_Task_Asset FOREIGN KEY (AssetID) references Asset (AssetID),
     constraint CK_Task_OneNonNull CHECK (
         (AreaID is null and AssetID is not null) or
         (AssetID is null and AreaID is not null))
 )

或者，你也可以让AreaID不为空，在Asset表上添加一个唯一约束（AreaID，AssetID） ，然后使资产表的外键引用两列 - 这确保了如果提供了 AssetID，它会链接到属于正确 AreaID 的资产。 中总是有一个 AreaID，这也将简化问题 1 的答案

如果任务第三个问题

：如果不知道这一切将如何使用，则很难说。我不认为这是一个不合理的方法。

Answer 2

让我们从问题 3 开始，这是一个有效的方法吗？如果它模拟现实的话那就是了。如果楼层、区域和资产是不同的事物，具有比共同属性更多的不同属性，并且如果对它们的操作往往不同，那么您的 3 表方法就很好。

对于第二个问题，只需将外键放在areaId和assetId上即可。外键中可以使用空值。

但是您需要一个表级约束，不允许它们都为空，也不允许它们都被填充。

最后，解决了模式和约束问题后，我们就可以进行查询了，这非常简单。这个想法是，一些任务通过资产通过区域连接到楼层，而其他任务则通过区域直接连接到楼层。为此，您需要将两个查询合并在一起：

-- First query is the three level
select task.*
  from task
  join asset on task.assetid = asset.id
  join area  on asset.areaid = area.id
  join floor on area.floorid = floor.id
 where floor = xxxx
UNION ALL
-- Second query is the two level
select task.*
  from task
  join area  on task.areaid  = area.id
  join floor on area.floorid = floor.id
 where floor = xxxx

这将返回正确的结果，但可能不是最快的。在每个子查询上放置一个 WHERE 子句来过滤掉 NULL/NOT NULL 可能会加快速度，您必须对此进行调查。