CODEWITHSUNDEEP
sqlsql-serverconstraintsuser-defined-functions
user3197770
user3197770

Reputation: 43

SQL - How do you use a user defined function to constrain a value between 2 tables

First here's the relevant code:

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

So i want to use a UDF to constrain the watch_date in watchhistory between the subscription_start and subscription_end in customer. I can't seem to figure it out.

Upvotes: 2

Views: 1296

Answers (1)

EzLo
EzLo

Reputation: 14199

Check constraints can't validate data against other tables, the docs say (emphasis mine):

[ 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.

That being said, you can create a scalar function that validates your date, and use the scalar function on the check condition instead:

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

Now add your check constraint so it validates that the result of the function is 1:

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

This is not a direct link to the other table, but a workaround you can do to validate the date. Keep in mind that if you update the customer dates after the watchdate insert, dates will be inconsistent. The only way to ensure full consistency in this case would be with a few triggers.

Upvotes: 2

Related Questions