在两个可空的FK之间添加SQL XOR约束 [英] Add a SQL XOR Constraint between two nullable FK's

问题描述

我想在一个表中的两个可空的FK之间定义一个约束，如果一个为null，另一个需要一个值，但两者都不能为空，并且都不能有值。逻辑是派生表从两个FK表中继承数据来确定其类型。另外，对于有趣的积分，这是一个坏主意吗？

I'd like to define a constraint between two nullable FK's in a table where if one is null the other needs a value, but both can't be null and both can't have values. Logic is the derived table inherits data from the either of the FK tables to determine its type. Also, for fun bonus points, is this a bad idea?

推荐答案

实现它的一个方法是简单地写下异或实际上意味着：

One way to achieve it is to simply write down what "exclusive OR" actually means:

CHECK (
    (FK1 IS NOT NULL AND FK2 IS NULL)
    OR (FK1 IS NULL AND FK2 IS NOT NULL)
)

但是，如果你有许多FKs，上面的方法可以很快变得笨拙，在这种情况下你可以这样做：

However, if you have many FKs, the above method can quickly become unwieldy, in which case you can do something like this:

CHECK (
    1 = (
        (CASE WHEN FK1 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK2 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK3 IS NULL THEN 0 ELSE 1 END)
        + (CASE WHEN FK4 IS NULL THEN 0 ELSE 1 END)
        ...
    )
)

BTW，该模式有合法用途，例如这一个（尽管由于缺少延迟约束而不适用于MS SQL Server）。无论您在特定情况下是否合法，我都无法根据您迄今提供的信息进行判断。

BTW, there are legitimate uses for that pattern, for example this one (albeit not applicable to MS SQL Server due to the lack of deferred constraints). Whether it is legitimate in your particular case, I can't judge based on the information you provided so far.

这篇关于在两个可空的FK之间添加SQL XOR约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！