check date in create table sql

edit: version 8.0.20

I’m learning sql just now, I try to fix it but I’m not able to do it. I have 3 table

create table order(
    id_order int primary key,
    order_date date
);
create delivery(
    id_delivery int primary key,
    delivery_date date,
    cod_order int,
    foreign key (cod_order) references order(id_order)
);
create purchased_product(
    id_product int,
    cod_order int,
    return_date date,
    foreign key (cod_order) references order(id_order)
    primary key (id_product, cod_order)
);

This is an easier scheme but it’s enough. The return_date must be less than delivery_date + 30 days I tried to use a check on return_date but no work, so I created a view to select the delivered products only

create view product_order_delivery(date_delivered_product) as
select date_delivery
from purchased_product P right join (select date_delivery, id_order
                                    from order right join delivery
                                    on order.id_order = delivery.cod_order
                                    where date_delivery is not null) OD
on P.cod_order = OD.id_order

and I tried again with check on return_date.

Can someone help me to understand how fix it?

thanks in advance 🙂

Answer

Two common options for solving this are:

1. Write a trigger to be sure the condition is true.
2. Write a user-defined function and use that in a check constraint.

An alternative method might suffice in your case. Change the definition of return_date so it is days_to_return. Then you can use a check constraint:

check (days_to_return between 0 and 30)

Of course, to get the actual date, you will need to join the two tables to fetch the delivery date.

EDIT:

The insert trigger would look something like this:

delimiter $$
create trigger trig_purchased_product_return_date
    before insert on purchased_product
    for each row
begin
    select @delivery_date = delivery_date
    from delivery d
    where d.cod_order = new.cod_order;

    if new.return_date < @delivery_date or new.return_date > @delivery_date + interval 30 day then
        signal sqlstate '45000' set message_text = 'invalid return_date'
    end if
end;

delimiter ;