Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    23

    Unanswered: DB2 certification questions

    Which of the following CANNOT be used to restrict specific values from being inserted into a column in a particular table?
    a. check constraint
    b. referential constraint
    c. view
    d. index
    answer is view?
    thanks any help would be great!
    Last edited by yanqinghuang; 10-30-07 at 11:38.

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    no, I believe index, because with view you can specify where predicate and restrict data that apply to this predicate
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I would also answer index since the only thing it can do is restrict with unique which allows only one value to be inserted, but the value CAN be inserted.

    A view only restricts if the view is being used to do the insert. If the inserts are directly to the underlying table, the data will be inserted.

    Andy

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't know the correct answer either. The question says "Which of the following CANNOT be used to restrict specific values from being inserted into a column in a particular table?" Thus, one could argue that "views" is the answer if table is meant to be understood as "base table". But for "index" speaks the wording "specific value" because - as Andy said - an index allows any value at least once.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    what a confusing question... This question requires some explanations.
    Grofaty

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    They tell you to provide the "best" answer". As stated above, a unique index can only prevent dups, not prevent a specific value from being inserted. It appears that all of the other answers can prevent a specific value (if set up correctly) from being inserted.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    I would say "views". In views you cannot restrict inserting values. You can restrict on what columns you can have but not the values.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That's not correct; you can restring inserting values via views. If you insert data through a view, you can implement values-based constraints:
    Code:
    CREATE TABLE t ( a INT, b INT )@
    
    CREATE VIEW v AS
       SELECT a, b
       FROM   t
       WHERE  a >= 5
       WITH CHECK OPTION@
    
    INSERT INTO v VALUES (5, 1), (6, 2)@
    DB20000I  The SQL command completed successfully.
    
    INSERT INTO v VALUES (4, 3)@
    SQL0161N  The resulting row of the insert or update operation does not conform to the view definition.  SQLSTATE=44000
    The point is that you have to insert into the view (a view is just a table) and not the underlying base table. What is not clear with the initial question is the meaning of the phrase "inserted into a ... particular table". Does it mean that the INSERT operation goes directly against the base table and the base table only? Or can it be directed against the view instead?

    Thinking about it, I'd say that "index" is probably the expected answer. The question doesn't say unique index (and a non-unique index doesn't restrict anything), and one could control access to the base table with privileges and only allow data modifications through the view. In fact, you could rename the base table and create the view with the original base table name. Thus, applications wouldn't even know the difference (assuming that static SQL statements are rebound).
    Last edited by stolze; 11-01-07 at 15:19.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Aug 2007
    Posts
    2

    answer?

    They typed "specific value". For example: how you should best deny to user insert value 'xxx' into column ?
    And this can not be achieved by any index.

Posting Permissions

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