SQL：我可以编写一个 CHECK 约束来使用外键验证外表中的数据吗？

Question

我正在 SQL Server 2008 R2 和/或 SQL Azure 中设计一个测试数据库。 （到目前为止，我的所有代码都将在两者上运行。）

我有一个带有外键的表，我需要添加一个引用外部表中字段的约束。

通常我会让外部表管理它自己的验证检查，但在某些情况下这是不可能的（或不合逻辑的）。我提供了一些示例代码来显示我想要完成的任务。

CREATE TABLE CustomerOrder
(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    CustomerID INT NOT NULL UNIQUE
        FOREIGN KEY REFERENCES Customer(ID)
        ON DELETE NO ACTION ON UPDATE CASCADE,
    ProductID INT NOT NULL UNIQUE
        FOREIGN KEY REFERENCES Product(ID)
        ON DELETE NO ACTION ON UPDATE CASCADE,
    Quantity INT NOT NULL DEFAULT 1,
    IsPaid BIT NOT NULL DEFAULT 0
)
GO

CREATE TABLE RMA
(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    CustomerOrderID INT NOT NULL UNIQUE
        FOREIGN KEY REFERENCES CustomerOrder(ID)
        ON DELETE NO ACTION ON UPDATE CASCADE,

    -- Add constraint to prevent RMAs from being
    -- created for orders that have not been paid.
    -- This could be a column constraint, or a table constraint.

    CHECK ( CustomerOrderID.IsPaid = 1 )

    -- ERROR: 'The multi-part identifier "CustomerOrderID.IsPaid"
    --         could not be bound.'
)
GO

在此示例中，将 CHECK 约束放入 CustomerOrder 表中是没有意义的，因为只要订单没有 RMA，CustomerOrder 表中的行就很乐意未付款。此外，CustomerOrder 表中的约束仍然需要引用 RMA 表来确认是否存在 RMA，因此仍然存在相同的问题。

我也尝试过：

CHECK (EXISTS(SELECT co.ID FROM CustomerOrder co
              WHERE co.ID=CustomerOrderID AND
                    co.IsPaid=1))
-- ERROR: 'Subqueries are not allowed in this context.
--         Only scalar expressions are allowed.'

由于这是基本数据验证的静态约束，并且永远不会被任何其他对象引用，因此我想避免将其变成标量函数或存储过程。
但是，为了避免使用标量函数，我需要在 SQL 中定义约束（最好是在部署数据库并创建表的同时）。

我可以在这里使用什么 SQL 语法来定义这种类型的约束？

Answer 1

另一种选择（因为您要求不添加触发器或扩大外键的解决方案）是删除 IsPaid 列并添加另一个用于已付款订单的表：

CREATE TABLE CustomerOrder
(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    CustomerID INT NOT NULL UNIQUE
        FOREIGN KEY REFERENCES Customer(ID)
        ON DELETE NO ACTION ON UPDATE CASCADE,
    ProductID INT NOT NULL UNIQUE
        FOREIGN KEY REFERENCES Product(ID)
        ON DELETE NO ACTION ON UPDATE CASCADE,
    Quantity INT NOT NULL DEFAULT 1
)
GO

CREATE TABLE CustomerOrderPaid
(
    ID INT NOT NULL PRIMARY KEY
        FOREIGN KEY REFERENCES CustomerOrder(ID)
        ON DELETE NO ACTION ON UPDATE CASCADE
)
GO

CREATE TABLE RMA
(
    ID INT NOT NULL IDENTITY PRIMARY KEY,
    CustomerOrderID INT NOT NULL UNIQUE
        FOREIGN KEY REFERENCES CustomerOrderPaid(ID)
        ON DELETE NO ACTION ON UPDATE CASCADE,
)
GO