SQL - 如何使用用户定义的函数来约束 2 个表之间的值

Question

首先是相关代码:

create table customer(
   customer_mail_address   varchar(255)   not null,
   subscription_start      date           not null,
   subscription_end        date,          check (subscription_end !< subcription start)
   constraint pk_customer primary key (customer_mail_address)
)

create table watchhistory(
   customer_mail_address   varchar(255)   not null,
   watch_date              date           not null,
   constraint pk_watchhistory primary key (movie_id, customer_mail_address, watch_date)
)

alter table watchhistory
    add constraint fk_watchhistory_ref_customer foreign key (customer_mail_address)
        references customer (customer_mail_address)
    on update cascade
    on delete no action
go

所以我想使用 UDF 来约束 watch_date 在 之间的 watch 历史订阅开始和 subscription_end 在客户。我似乎无法弄清楚。

Answer 1

检查约束无法针对其他表验证数据，docs说(强调我的):

[ CONSTRAINT constraint_name ]   
{   
  ...
  CHECK [ NOT FOR REPLICATION ] ( logical_expression )  
}

logical_expression

Is a logical expression used in a CHECK constraint and returns TRUE or FALSE. logical_expression used with CHECK constraints cannot reference another table but can reference other columns in the same table for the same row. The expression cannot reference an alias data type.

话虽如此，您可以创建一个标量函数来验证您的日期，并在检查条件上使用标量函数:

CREATE FUNCTION dbo.ufnValidateWatchDate (
    @WatchDate DATE,
    @CustomerMailAddress VARCHAR(255))
RETURNS BIT
AS
BEGIN

    IF EXISTS (
        SELECT
            'supplied watch date is between subscription start and end'
        FROM
            customer AS C
        WHERE
            C.customer_mail_address = @CustomerMailAddress AND
            @WatchDate BETWEEN C.subscription_start AND C.subscription_end)
    BEGIN
        RETURN 1
    END

    RETURN 0

END

现在添加您的检查约束，以便验证函数的结果为 1:

ALTER TABLE watchhistory 
    ADD CONSTRAINT CHK_watchhistory_ValidWatchDate 
    CHECK (dbo.ufnValidateWatchDate(watch_date, customer_mail_address) = 1)

这不是到另一个表的直接链接，而是您可以用来验证日期的解决方法。请记住，如果您更新 customer在 watchdate 插入之后的日期，日期将不一致。在这种情况下确保完全一致性的唯一方法是使用一些触发器。