Emulating the CHECK clause in MariaDB

Versione italiana

MariaDB (like MySQL and its forks) does not have a CHECK clause in the CREATE TABLE statement. But you can emulate it using VIEWs.

Data Definition

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 WHERE condition.

Data Manipulation

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', 'mario.rossi@rossionline.it');

The row is successfully inserted!

You can 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.

Enjoy!

See also:

Advertisements

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s