Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12

    Unanswered: Question about views with check option

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Because, you specified "WITH LOCAL CHECK OPTION".
    If you specified CASCADED(this is a default), "INSERT INTO v2 VALUES (5);" will fail.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    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...

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by tonkuma View Post
    Do you mean what example?
    The ones I had in my initial posting (those from the assessment test)

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    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.
    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".
    Last edited by tonkuma; 12-14-09 at 10:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •