I wrote a post about how to emulate the CHECK
clause in MariaDB and MySQL using a VIEW
. Here is a different approach: this time, we will use TRIGGER
s to reach the same goal. And then we will discuss which approach is better.
Let’s CREATE what we need
Let’s CREATE
a simple table:
-- make sure that `person` can be created
DROP VIEW IF EXISTS `person`;
DROP TABLE IF EXISTS `person`;
-- table whose data must be validated
CREATE TABLE `person`
(
`id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL,
`email` char(80) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE = Aria;
`person` contains a Primary Key, a field called `name`, and `email`. We will just care about the `email`. We want to validate it using a LIKE
pattern. But how?
Let’s CREATE the TRIGGER
TRIGGER
s can be set on the base DML operations (INSERT
, UPDATE
, DELETE
). If we want to be sure that data are valid, we should use both INSERT
and UPDATE
TRIGGER
s, but for brevity here I will just show the INSERT TRIGGER
. TRIGGER
s can be executed BEFORE
or AFTER
the operation they are associated to; of course, if we want to prevent invalid data to be written, we need the BEFORE
option.
-- validate email
CREATE TRIGGER `person_validate_insert`
BEFORE INSERT
ON `person`
FOR EACH ROW
BEGIN
IF NEW.`email` NOT LIKE '%_@%_.__%' THEN
SIGNAL SQLSTATE VALUE '45000'
SET MESSAGE_TEXT = '[table:person] - `email` column is not valid';
END IF;
END;
TRIGGER
s can’t have a comment, so I always try to use descriptive names. The first word is person, which is the name of the table; then, I explain what the TRIGGER
does: ‘validate insert’.
The TRIGGER
checks if the new `email` is valid. If it is, execution continues and the INSERT
can be performed. But if it is not, an error is thrown. This stops the execution, and the INSERT
will not be performed.
Just a note about the error. The SQLSTATE
is ‘45000’, as suggested by MySQL’s documentation – user-defined errors should set SQLSTATE
to ‘45000’. The message starts with an info wrapped between the [square brackets]. When I throw an error from a Stored Routine, I write the name of the routine; when I throw it from a TRIGGER
, I write the table name. Then, there is a descriptive error message. “Descriptive” means that the user is told what he was trying to do (INSERT
) and why the operation aborted (`email` column is not valid).
Let’s test!
I assume that everything I don’t test (if it can be tested), simply does not work. So, let’s INSERT
a valid row:
MariaDB [test]> INSERT INTO `person`
-> (`name`, `email`)
-> VALUES
-> ('Emiliano Zapata', 'zapata@revolucion.mx');
Query OK, 1 row affected (0.59 sec)
Great! Now, let’s see what happens when we try to INSERT
invalid data:
MariaDB [test]> INSERT INTO `person`
-> (`name`, `email`)
-> VALUES
-> ('John Doe', 'misterdoe@nowhere');
ERROR 1644 (45000): [table:person] - `email` column is not valid
Great! That’s exactly the error we expected to see.
What is the best technique?
Now that I discussed two different techniques (read how to use a VIEW
), I am supposed to explain which one is better. But I’ll just write some notes, that should clarify the pro’s and the con’s of each method:
* If you are using MariaDB 5.3 or older, or MySQL 5.4 or older, the SIGNAL
statement is not avaible. You can use a SELECT
on a table which does not exists, which is a bit less clean (you know why you see that error, but it can be confusing for other developers).
* Hosting companies often do not allow you to use TRIGGER
s – in that case, you can only use the VIEW
technique.
* The INSERT
/UPDATE
‘s performance should not be notably affected by this choice. DELETE
and SELECT
performance may be a little bit slower for the VIEW
method.
* The TRIGGER
method needs 2 different triggers (on insert, on update; maybe on delete, if you want to check data on other tables to ensure database’s consinstency). The VIEW
method needs a view and renamed table. The second option is probably more convenient… but this is TRUE
for me, and may be FALSE
for you. (a boolean way to say that it’s subjective)
* This also means that the TRIGGER
method requires duplicated code (same statements in INSERT
and in UPDATE
TRIGGER
s). You can avoid this issue by using a Stored Routine, but it is slower.
Since there is not an important difference in performances or ease of mantainence of these methods, the choice is up to you.
Enjoy!
“the TRIGGER method requires duplicated code”
that is enough to me. VIEW, here i come!
If a view is suitable for your needs, I completely agree. But in triggers you can write more complex code.
Reblogged this on Sutoprise Avenue, A SutoCom Source.