Database:MariaDB constraints triggers

From Juneday education
Jump to: navigation, search

Constraints in MariaDB

Regexp in place of globbing

MariaDB (prior to version 10.2) doesn't implement check constraints (it parses them but then silently ignores them).

For those readers/students who wants a way to implement check constraints, we here provide a short introduction to using triggers and some other constraints below.

As an example, we take a table cars where we want a constraint on the column license_number which enforces that only license numbers with a valid format according the the Swedish format for license numbers ( [A-Z][A-Z][A-Z] [0-9][0-9][0-9] ), three capital letters between A-Z followed by a space followed by three numbers between 0-9.

Example trigger

Here's how you could create such a trigger, which will check all license_number values before accepting an INSERT:

DELIMITER //
CREATE TRIGGER valid_license_number BEFORE INSERT ON cars FOR EACH ROW
BEGIN
IF NOT NEW.license_number regexp '^[A-Z]{3}\\ [0-9]{3}' THEN
    signal sqlstate '66666' SET MESSAGE_TEXT = 'Malformed license number';
END IF;
END; //
DELIMITER ;

Example run - proof-of-concept

Here's an example run without the trigger and then with the trigger:

MariaDB [cars]> insert into cars(license_number) values('ABC 123');
Query OK, 1 row affected (0.01 sec)

MariaDB [cars]> insert into cars(license_number) values('ABCapa 123999');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [cars]> select * from cars;
+----+----------------+
| id | license_number |
+----+----------------+
|  6 | ABC 123        |
|  7 | ABCapa         |
+----+----------------+
2 rows in set (0.00 sec)
MariaDB [cars]> delete from cars where id=7;
Query OK, 1 row affected (0.01 sec)

MariaDB [cars]> DELIMITER //
MariaDB [cars]> CREATE TRIGGER valid_license_number BEFORE INSERT ON cars FOR EACH ROW
    -> BEGIN
    -> IF NOT NEW.license_number regexp '^[A-Z]{3}\\ [0-9]{3}' THEN
    ->     signal sqlstate '66666' SET MESSAGE_TEXT = 'Malformed license number';
    -> END IF;
    -> END; //
Query OK, 0 rows affected (0.01 sec)

MariaDB [cars]> DELIMITER ;
MariaDB [cars]> insert into cars(license_number) values('ABCapa 123999');
ERROR 1644 (66666): Malformed license number
MariaDB [cars]> insert into cars(license_number) values('EVL 666');
Query OK, 1 row affected (0.00 sec)

MariaDB [cars]> select * from cars;
+----+----------------+
| id | license_number |
+----+----------------+
|  6 | ABC 123        |
|  8 | EVL 666        |
+----+----------------+
2 rows in set (0.00 sec)

MariaDB [cars]>

Explanation of the regular expression

The regexp (regular expression) ^[A-Z]{3}\\ [0-9]{3} means, broken down:

  • ^ "starts with"
  • [A-Z] "any letter between A to Z"
  • {3} "exactly three of them"
  • \\ "a single space" (the space is escaped with backslash and the backslash is also escaped with backslash)
  • [0-9] "any character between 0 and 9"
  • {3} "exactly three of them"

So, the string (the left-hand operand to the regexp must start with three capital letters, followed by a space, followed by three number characters.

We hope you'll have use for this MariaDB difference hint! Try it out!

Foreign key (short version)

Here's a short version of how to use foreign key constraints in MariaDB. Let's start with some table definitions:

create table publishers(id int(11) not null primary key auto_increment,
                        name varchar(30) not null
);
create table books(id int(11) not null primary key auto_increment,
                   title varchar(30) not null,
                   publisher_id int(11),
                   constraint foreign key (publisher_id) references publishers (id));

Next, lets investigate the data of the books and publishers tables (this time in the interactive shell):

MariaDB [cars]> describe publishers;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
MariaDB [cars]> select * from publishers;
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | Studentlitteratur |
|  2 | Bonnier           |
+----+-------------------+
2 rows in set (0.00 sec)

MariaDB [cars]> describe books;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| title        | varchar(30) | NO   |     | NULL    |                |
| publisher_id | int(11)     | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [cars]> select * from books;
+----+---------------+--------------+
| id | title         | publisher_id |
+----+---------------+--------------+
|  2 | Java indirekt |            1 |
+----+---------------+--------------+
1 row in set (0.00 sec)

Next, let's try to insert a new book with an invalid publisher id, and see what happens:

MariaDB [cars]> INSERT INTO books(title, publisher_id) VALUES('Set theory for amateurs', 666);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cars`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`publisher_id`) REFERENCES `publishers` (`id`))
MariaDB [cars]>

Finally, let's insert that book with a proper publisher_id like 1 (Studentlitteratur):

MariaDB [cars]> INSERT INTO books(title, publisher_id) VALUES('Set theory for amateurs', 1);
Query OK, 1 row affected (0.00 sec)

MariaDB [cars]>

This shows the basics of foreign keys in MariaDB. There are lots of more advanced features, which you can read about elsewhere when you need to know about them. We hope you had use for this mini-introduction.

Further reading

Navigation

Back to the SQLite version page (Adding constraints...)