mpopp.net blog – Markus Popp

After trying to emulate check constraints with a trigger yesterday, I have now tried out if it works to enforce data integrity with views, as Arjen Lentz described it in this article: http://arjen-lentz.livejournal.com/49881.html.

I created a little table and a view with a WHERE clause that defined the conditions – and I added the WITH CHECK OPTION clause:

mysql> CREATE TABLE checktest (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   val INT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v_checktest AS
    ->   SELECT id, val FROM checktest
    ->   WHERE val >= 1 AND val    WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO v_checktest (val) VALUES (2);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO v_checktest (val) VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v_checktest (val) VALUES (6);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v_checktest'
mysql> SELECT * FROM v_checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
|  2 |   4 |
+----+-----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
|  2 |   4 |
+----+-----+
2 rows in set (0.00 sec)

So far, it works fine, but what happens, if I try to update the second row to value 6 (using the view, of course):

mysql> UPDATE v_checktest SET val=6 WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM v_checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
+----+-----+
1 row in set (0.00 sec)

mysql> SELECT * FROM checktest;
+----+-----+
| id | val |
+----+-----+
|  1 |   2 |
|  2 |   6 |
+----+-----+
2 rows in set (0.00 sec)

That was unfortunately not, what I wanted. My question: is this a bug that should be reported?

I found out another interesting thing when I entered SHOW VIEW v_checktest (I formatted the output a bit to make it more readable):

mysql> SHOW CREATE VIEW v_checktest\G
*************************** 1. row ***************************
       View: v_checktest
Create View: CREATE ALGORITHM=UNDEFINED 
  DEFINER=`mpopp`@`localhost` 
  SQL SECURITY DEFINER VIEW `v_checktest` AS 
select `checktest`.`id` AS `id`,
  `checktest`.`val` AS `val` 
  from `checktest` 
  where ((`checktest`.`val` >= 1) 
    and (`checktest`.`val` <= 5)) 
  WITH CASCADED CHECK OPTION
1 row in set (0.00 sec)

Even though I didn’t specify CASCADED in the WITH CHECK OPTION clause, it’s now here in the view definition. Another bug?

By the way – what can you do, if you want to get the original view definition? I don’t think, there’s a way inside MySQL, but if you have access to the file sytem where MySQL runs at, you can open the .frm file of the view. For example, my MySQL installation resides at D:\mysql, the view is in the database ‘test’ and the view is called ‘v_checktest’. So the file is D:\mysql\data\test\v_checktest.frm.

Here’s the content of this file:

TYPE=VIEW
query=select `test`.`checktest`.`id` AS `id`,
  `test`.`checktest`.`val` AS `val` 
from `test`.`checktest` 
where ((`test`.`checktest`.`val` >= 1) 
  and (`test`.`checktest`.`val` = 1 AND val <= 5\n  
  WITH CHECK OPTION

Here at the end, you find the original definition, like I defined the view.

I hope that the original view definition will soon be available through MySQL clients, too. There is already a feature request for it and there seem to be a lot of people (me included) who really wait for it.