SQL Server - Check constraints
SQL Server provides check constraints feature. 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, it would be ideal to use check constraints . However, MySQL does not implement check constraints completely (it only implements constraints for primary key, foreign key, NOT NULL and UNIQUE ). MySQL even understands their syntax, but does not actually implement them, as described in the manual :
The% of 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 ( CHECK ) and the other for change ( BEFORE INSERT ):
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 a view . In this case you should do insertions and changes (% with% s and% with% s) only through view . Doing this right in the base table will bypass the checks. Here is 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 BEFORE UPDATE that is validly populated only when the dates are in the correct range and with INSERT otherwise it will result in an error due to UPDATE and prevent insertion or change. Here's 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 type NOT NULL .
Observations
To keep the concept simple, I just put the test of the date of entry being before the date of exit ( NULL ). To complete the test, I think you would need several conditions:
You can combine all of them (or selectively some of them) into a single expression by wrapping them with parentheses and matching% s with% s, but I think it's easier to keep them separate. Each of these conditions would become a data_entrada IS NULL OR year(data_entrada) >= 2000 within triggers or a separate virtual column. In the case of view , you would be required to combine them into a single expression in the data_saida IS NULL OR year(data_saida) >= 2000 clause.
MariaDB
MariaDB is a MySQL fork , created after Sun acquired (along with MySQL) by Oracle. It was developed by the same original MySQL creator, who decided to create fork for rejecting Oracle by taking over the project.
MariaDB implements the check constraints that MySQL has left out since version 10.2. 1 . The syntax must be the same as that of SQL Server.
Bibliographic references:
