Question about views with check option

shammat · #1 (**permalink**) 12-14-09, 06:56

Hi,

while going through the preparation for a DB2 certification I ran across the following question.

The sample tables and views are setup as follows:

CREATE TABLE tab1 (col1 SMALLINT );
CREATE VIEW v1 AS SELECT col1 FROM tab1 WHERE col1 > 20 ;
CREATE VIEW v2 AS SELECT col1 FROM v1 WITH LOCAL CHECK OPTION ;
CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 < 50 ;

Then the following insert statements were shown:

INSERT INTO v1 VALUES (10);
INSERT INTO v2 VALUES (5);
INSERT INTO v3 VALUES (50);
INSERT INTO v3 VALUES (100);

The actual question was: which statement will fail?

My understanding is that the second statement should fail.

But when I tried that on my DB2 Express-C installation (on Windows) no statement failed.

Is this a limitation of Express-C that it doesn't enforce the check?

Or is there anything else that I'm missing?

tonkuma · #2 (**permalink**) 12-14-09, 07:27

Because, you specified "WITH LOCAL CHECK OPTION".
If you specified CASCADED(this is a default), "INSERT INTO v2 VALUES (5);" will fail.

tonkuma · #3 (**permalink**) 12-14-09, 07:32

Please see this example:

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE tab1 (col1 SMALLINT );
CREATE VIEW v1 AS SELECT col1 FROM tab1 WHERE col1 > 20 ;
CREATE VIEW v2L AS SELECT col1 FROM v1 WITH LOCAL CHECK OPTION ;
CREATE VIEW v2C AS SELECT col1 FROM v1 WITH CASCADED CHECK OPTION ;
CREATE VIEW v3L AS SELECT col1 FROM v2L WHERE col1 < 50 ;
CREATE VIEW v3C AS SELECT col1 FROM v2C WHERE col1 < 50 ;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

Code:

------------------------------ Commands Entered ------------------------------
INSERT INTO v1 VALUES (10);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO v2L VALUES (5);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO v2C VALUES (5);
------------------------------------------------------------------------------
INSERT INTO v2C VALUES (5)
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0161N  The resulting row of the insert or update operation does not conform 
to the view definition.  SQLSTATE=44000

Code:

------------------------------ Commands Entered ------------------------------
INSERT INTO v3L VALUES (50);
INSERT INTO v3C VALUES (50);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO v3L VALUES (100);
INSERT INTO v3C VALUES (100);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

shammat · #4 (**permalink**) 12-14-09, 08:03

Thanks for the answer, think I understand it now.
But essentially that means that indeed no statement from the examples should fail?

This is interesting, because this was part of an assesment test I took (as a preperation for a certification exam) , and I don't think there was an option to specify "none fails"...
Maybe an error in the test...

tonkuma · #5 (**permalink**) 12-14-09, 08:18

Quote:

But essentially that means that indeed no statement from the examples should fail?

Do you mean what example?

In the examle I showed,
v2C was created with "WITH CASCADED CHECK OPTION".
And "INSERT INTO v2C VALUES (5);" was failed.
v2L was created with "WITH LOCAL CHECK OPTION".
And "INSERT INTO v2L VALUES (5);" was successful.

That worked as I expected.

shammat · #6 (**permalink**) 12-14-09, 08:57

Quote:

Originally Posted by tonkuma

Do you mean what example?

The ones I had in my initial posting (those from the assessment test)

tonkuma · #7 (**permalink**) 12-14-09, 09:16

Quote:

But essentially that means that indeed no statement from the examples should fail?

Yes! No statement in your examples should fail.

Because, you specified LOCAL.

Quote:

CREATE VIEW v2 AS SELECT col1 FROM v1 WITH LOCAL CHECK OPTION ;

If you want to let DB2 check, you should specify CASCADED which I already wrote.

Quote:

Because, you specified "WITH LOCAL CHECK OPTION".
If you specified CASCADED(this is a default), "INSERT INTO v2 VALUES (5);" will fail.

"(this is a default)" means if you specified "WITH CHECK OPTION", it implies "WITH CASCADED CHECK OPTION".

Internet Services

Web Development

Digital Marketing

Consumer Tech