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

Question

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.

Please add the rdms you are using. On Oracle for example you can't use check constraints which refer to another table: "The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables." — Thomas Strub, Nov 19 '19 at 13:00
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product (and `!<` is not a valid SQL operator). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using — , Nov 19 '19 at 13:01
Possible duplicate of [Can a Check constraint relate to another table?](https://stackoverflow.com/questions/3880698/can-a-check-constraint-relate-to-another-table) — Mahesh, Nov 19 '19 at 13:39
oh i see, i will take this thread down when i figured it out, thanks! — user3197770, Nov 19 '19 at 13:45
While you can do this in a roundabout way, the better question is why you think you should. The name "history" suggests that this table is not really integral to your model and is used to capture changing information (for auditing or forensic purposes perhaps). — SMor, Nov 19 '19 at 14:52
And subscription_end is nullable - does your check constraint (as strangely expressed as it is) work in all situations? — SMor, Nov 19 '19 at 14:53

EzLo · Answer 1 · 2019-11-19T13:54:36.893

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.

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

1 Answers1