MariaDB (like MySQL and its forks) does not have a
CHECK clause in the
CREATE TABLE statement. But you can emulate it using
First, let’s create a table:
CREATE TABLE `t_person` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT , `name` CHAR(30) NOT NULL , `surname` CHAR(30) NOT NULL , `email` CHAR(50) NOT NULL , PRIMARY KEY (`id`) );
The table is ready to store our data, but we need to check that data is valid. When new row is inserted, we want to check that `surname` contains at least 2 characters and `email` contains ‘@’ and ‘.’ characters.
So, let’s create a view that would show only data that follow these simple rules:
CREATE VIEW `person` AS SELECT * FROM `t_person` WHERE LENGTH(`surname`) > 1 AND `email` LIKE '%_@_%._%' WITH CHECK OPTION;
The view is
INSERTable, because it is based on only one basetable and has no grouping functions/clauses. The
WITH CHECK OPTION clause allow new rows to be inserted only if they satisfy the view’s
Now, you can insert data through the view. Let’s start with an invalid row:
INSERT INTO `person` (`name`, `surname`, `email`) VALUES ('Mario', 'Rossi', 'mario.rossi');
`email` does not contain a ‘@’, so the row is rejected with a good error message:
[Err] 1369 - CHECK OPTION failed 'test.person'
So, let’s try to insert a valid row:
INSERT INTO `person` (`name`, `surname`, `email`) VALUES ('Mario', 'Rossi', 'firstname.lastname@example.org');
The row is successfully inserted!
SELECT your data from `t_person` or from `person`, as you prefer. If you use `person`, than you (or other developers) can pretend that the view is a table and `t_person` does not exists – it’s transparent. However,
SELECTing rows from `person` adds a useless
WHERE that is always true but affects the performances.