An Introductory Guide to SQL/

...

/

Local & Cascaded Check

Local & Cascaded Check

This lesson looks at the two types of rule checking that can be used with the CHECK option clause.

We'll cover the following...

LOCAL AND CASCADED CHECK

Local and cascaded check clauses are used to determine the scope of rule testing when a view is created based on another view. To summarize, Local check option restricts the rule checking to only the view being defined whereas the Cascaded check option checks the rules of all underlying views. In the absence of these keywords, cascaded check is used as default.

Syntax

CREATE [OR REPLACE] VIEW view_name AS

select_statement

WITH [LOCAL | CASCADED] CHECK OPTION;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/44lesson.sh and wait for the MySQL prompt to start-up.

Press + to interact
-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 



-- CASCADED CHECK

-- Query 1

CREATE VIEW ActorsView1 AS

SELECT * 

FROM Actors 

WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) > 40;



-- Query 2

INSERT INTO ActorsView1 

VALUES (DEFAULT, 'Young', 'Actress', '2000-01-01', 'Female', 'Single', 000.00);



-- Query 3

CREATE OR REPLACE VIEW ActorsView2 AS

SELECT * 

FROM ActorsView1

WITH CASCADED CHECK OPTION; 



-- Query 4

INSERT INTO ActorsView2 

VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);



-- Query 5

CREATE OR REPLACE VIEW ActorsView3 AS

SELECT * 

FROM ActorsView2

WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) < 50;  



-- Query 6

INSERT INTO ActorsView3 

VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);



-- Query 7

INSERT INTO ActorsView3 

VALUES (DEFAULT, 'Old', 'Actor', '1960-01-01', 'Male', 'Single', DEFAULT);



-- LOCAL CHECK 

-- Query 8

ALTER VIEW ActorsView2 AS

SELECT * 

FROM ActorsView1

WITH LOCAL CHECK OPTION; 



-- Query 9

INSERT INTO ActorsView2 

VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);



-- Query 10

INSERT INTO ActorsView3 

VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);
Terminal 1
Terminal
Loading...

Cascaded Check

  1. We will start by creating a view ActorsView1 which shows all actors who are older than 40.

    CREATE VIEW ActorsView1 AS
SELECT * 
FROM Actors 
WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) > 40;

    Seven rows from the Actors table satisfy the WHERE clause.

In the absence of the WITH CHECK OPTION clause there is no restriction on updates through ActorsView1. We can insert a 20-year-old actor to the Actors table using this view as follows:

INSERT INTO ActorsView1 
VALUES (DEFAULT, 'Young', 'Actress', '2000-01-01', 'Female', 'Single', 000.00);

The record is inserted in the table even though it does ...