Date: 2005-10-26 09:59 Subject: Using an updatable VIEW as a constraint Security: Public

Let me just show you this first:

mysql> USE test
mysql> CREATE TABLE con (i INT);
mysql> CREATE VIEW viewcon AS
    ->   SELECT * FROM test.con
    ->    WHERE i BETWEEN 10 AND 20
    ->   WITH CASCADED CHECK OPTION;
mysql> INSERT INTO viewcon VALUES (30);
ERROR 1369 (HY000): CHECK OPTION failed 'test.viewcon'

So, you can do detailed server-side data validation with a view that has the CASCADED CHECK OPTION enabled. We can't insert a value of 30 because it's not within the 10-20 range specified in the view.

Of course, this only works because nearly all MySQL views are actually updatable (where possible). That means that you can do INSERTs, UPDATEs and DELETEs on a view! Think about the fab implications of that for a moment. Because it works like a regular WHERE clause, you could perform complex checks that involve multiple columns together, for instance.

Interesting and useful, wouldn't you agree?

Post A Comment | Add to Memories | Tell a Friend | Link