Check or Constraint

Question

Talk, guys, good morning

I was wondering if you could do the following: I need to make sure that the data entered in a date column is between two dates that are in another table.

Example:

create table tb_lote(
    pk_id_lote int primary key auto_increment,
    fk_cod_evento int not null,
    prc_lote double not null,
    qtd_ingresso int not null,
    dta_inicio date not null,
    dta_final date not null,
    constraint fk_evento_lote foreign key(fk_cod_evento) references tb_evento(pk_id_evento)

);

create table tb_ingresso(
    pk_id_ingresso int primary key auto_increment,
    fk_cod_lote int not null,
    fk_cpf_pessoa varchar(14) not null,
    dta_venda date not null,
    constraint fk_ingresso_lote foreign key(fk_cod_lote) references tb_lote(pk_id_lote),
    constraint fk_pessoa_ingresso foreign key(fk_cpf_pessoa) references tb_pessoa(pk_cpf_pessoa),
    check(dta_venda >= tb_lote.dta_inicio and dta_venda <= tb_lote.dta_final)
);

When executing the code below, it generates the following error for those who are in doubt if it works:

Error Code: 3820. Check constraint 'tb_ingresso_chk_1' refers to non-existing column 'dta_inicio'.