How to restrict dates in Mysql and SQL Server?

Question

I wonder if I can restrict certain dates in the database itself, not in the code.

For example, I have a table that records date of entry and date of exit. Here are some requirements.

• The date of entry can never be higher than the date of exit
• The date of entry may not be less than a given year (2000 for example)
• I can’t have an exit date without an entry date

What I want to know is whether these restrictions can be done in the database itself and not in the application.

My database is Mysql, but it would be nice to know the possibility for SQL Server as well.

Answer 1

SQL Server - Check constraints

SQL Server provides the check constraints. To add a check Constraint, you do the following:

ALTER TABLE sua_tabela ADD CONSTRAINT CK_nome_da_sua_constraint
CHECK (data_entrada <= data_saida)

Mysql

In Mysql, the ideal would be to use check constraints. However, Mysql does not implement check constraints completely (it implements only constraints primary key, foreign key, NOT NULL and UNIQUE). Mysql even understands their syntax, but does not actually implement them, as described in manual:

The CHECK clause is Parsed but Ignored by all Storage Engines.

So there are three possibilities:

1. Triggers:

The idea is to put two triggers to validate the data, one for insertion (BEFORE INSERT) and the other for amendment (BEFORE UPDATE):

DELIMITER $
CREATE TRIGGER validar_datas_insert BEFORE INSERT ON sua_tabela
FOR EACH ROW
BEGIN
    IF NOT (new.data_entrada <= new.data_saida)
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Datas inválidas';
    END IF;
END$
DELIMITER ;

DELIMITER $
CREATE TRIGGER validar_datas_update BEFORE UPDATE ON sua_tabela
FOR EACH ROW
BEGIN
    IF NOT (new.data_entrada <= new.data_saida)
    THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Datas inválidas';
    END IF;
END$
DELIMITER ;

2. Insert into a view with validation

Another idea is to use one view. In this case you must make insertions and changes (INSERTs and UPDATEs) only by means of view. Doing this right in the base table will bypass the checks. Here’s the code:

CREATE VIEW sua_view AS
SELECT * FROM sua_tabela WHERE data_entrada <= data_saida
WITH CHECK OPTION;

3. Virtual column

The idea is to add a dummy column NOT NULL which is valid only when the dates are in the correct range and with NULL otherwise which will result in an error due to the NOT NULL and prevent insertion or alteration. Here is an example:

ALTER TABLE sua_tabela ADD COLUMN
validar_data CHAR(0) AS
(CASE WHEN data_entrada <= data_saida THEN '' END)
VIRTUAL NOT NULL;

It is interesting to note that this column does not consume physical space in the database because it is of the type CHAR(0) VIRTUAL NOT NULL.

Remarks

To keep the concept simple, I just put the test of the input date before the exit date (data_entrada <= data_saida). To take the full test, I think you would need several conditions:

• data_entrada IS NULL OR data_saida IS NULL OR data_entrada <= data_saida

• data_entrada IS NULL OR year(data_entrada) >= 2000

• data_saida IS NULL OR year(data_saida) >= 2000

• NOT (data_entrada IS NULL AND data_saida IS NOT NULL)

It is possible to combine all of them (or selectively some of them) into a single expression by wrapping them in parentheses and matching with ANDs, but I think it’s easier to keep them apart. Each of these conditions would become one IF NOT (...) within the triggers or a separate virtual column. In the case of view, you would be bound to combine them into a single expression in the clause WHERE.

Mariadb

The Mariadb is a Fork Mysql, created after the acquisition of Sun (along with Mysql) by Oracle. It was developed by the same original Mysql creator, who decided to create the Fork by rejecting Oracle taking over the project.

Mariadb implements the check constraints that Mysql has not been wanting since version 10.2.1. Syntax must be the same as SQL Server syntax.

Bibliographical references:

• http://mysqlserverteam.com/new-and-old-ways-to-emulate-check-constraints-domain/

• https://technet.microsoft.com/pt-br/library/ms179491(v=sql.105). aspx

• https://jira.mariadb.org/browse/MDEV-7563