Mysql: Limit record recording based on value from another table

Question

I have a table A with a field start_date and another end_date.

I have a table B with a field date.

Is there any way to ensure that a record is only saved on B when B.date be among A.start_date and A.end_date?

I know how to handle insertion via PHP. But I want to know if there is something that can be done in the Bank that guarantees this type and integrity. I’m looking for something in the constraints' line of action (but any solution is welcome!)

Answer 1

There is, you can make one INSERT from a SELECT where in your clause WHERE shall be determined that the date has to be between the tabela_a then just limit the query to 1 to enter only 1 time the record, even finding more occurrences, for example:

CREATE TABLE tabela_a(
    start_date DATE, 
    end_date DATE
);
CREATE TABLE tabela_b(
    campo1 varchar(255), 
    campo2 varchar(255), 
    campo3 varchar(255), 
`date` DATE
);

INSERT INTO tabela_a(
    start_date, 
    end_date) 
VALUES
    ('2018-03-01', '2018-03-30'),
    ('2018-03-01', '2018-03-29'),
    ('2018-03-01', '2018-03-05');

Here will enter the records "valor1, valor2, Valor3, 2018-03-27", as found in tabela_a occurrences.

INSERT INTO tabela_b(campo1, campo2, campo3, `date`)
SELECT 
    'valor1',
    'valor2',
    'valor3',
    '2018-03-27'
FROM tabela_a
WHERE '2018-03-27' between start_date and end_date
LIMIT 1;

SELECT
    *
FROM tabela_b;

Here is a print of the query of what was inserted in tabela_b:

I think it meets your need.