Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    17

    Unanswered: FK not checked ?

    Hello !

    I'm working on DB2 v8 on z/OS.

    Here is my pbl.
    I have th following tables :
    - table TAB1
    . col1 CHAR(6) NOT NULL
    . col2 CHAR(1) NOT NULL
    PK : col1, col2

    - table TAB2
    . colY CHAR(6)
    . colZ CHAR(1)
    FK : ColY, ColZ to table TAB1

    NOTES : the column TAB1.col2 can support 3 values Blank, A and P. NULL is not allowed because of the primary Key.

    When I insert a record into table F like this :
    INSERT INTO TAB2
    (colY,
    colZ)
    VALUES
    ('BLABLA',
    NULL);
    The FK doesn't check an error whereas I don't have any record in table TAB1 with a NULL in the column col2!!

    Any idea ???
    Thanks !!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It has no contradiction.

    DB2 Universal Database for z/OS Version 8 SQL Reference
    Chapter 1. DB2 concepts
    Constraints
    Referential constraints
    P9
    The following rules provide referential integrity:

    insert rule
    A nonnull insert value of the foreign key must match some value of the
    parent key of the parent table. The value of a composite foreign key is null
    if any component of the value is null.
    Last edited by tonkuma; 11-26-09 at 06:40.

  3. #3
    Join Date
    May 2009
    Posts
    17
    Thanks for your answer !

    but is that means that if I do the following :
    INSERT INTO TAB2
    (colY,
    colZ)
    VALUES
    (NULL,
    'A');

    the result will be the same ??

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think... Yes!

    You should read second statement in my quote.
    > The value of a composite foreign key is null
    > if any component of the value is null.

    Then, read again first statement.
    > A nonnull insert value of the foreign key must match some value of the
    > parent key of the parent table.

    I couldn't find any other rule of referential integrity for insert.
    Last edited by tonkuma; 11-26-09 at 08:04.

  5. #5
    Join Date
    May 2009
    Posts
    17
    I've tried and I've got a -545 ! In this case the FK is checked !!

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    -545 violates check constraint.
    You can know which constraint violates by looking full message.

    DB2 Version 9.1 for z/OS Codes GC18-9843-04.
    -545
    THE REQUESTED OPERATION IS
    NOT ALLOWED BECAUSE A ROW
    DOES NOT SATISFY THE CHECK
    CONSTRAINT check-constraint

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    but is that means that if I do the following :
    INSERT INTO TAB2
    (colY,
    colZ)
    VALUES
    (NULL,
    'A');
    It worked fine on DB2 9.7 for Windows.
    So, there might be some more restrictions or functionalities on DB2 for z/OS.

    Make tables
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE table TAB1
    (col1 CHAR(6) NOT NULL
    ,col2 CHAR(1) NOT NULL
    ,PRIMARY KEY(col1, col2)
    ,CONSTRAINT tab1_check_col2
     CHECK (col2 IN (' ', 'A', 'P') )
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE table TAB2 
    (colY CHAR(6) 
    ,colZ CHAR(1)
    ,CONSTRAINT tab2_fk_tab1
     FOREIGN KEY (ColY, ColZ)
     REFERENCES tab1
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    INSERT data and results:
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO TAB2 
    (colY, colZ)
    VALUES
    ('BLABLA', NULL);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO TAB2 
    (colY, colZ)
    VALUES
    (NULL, 'A');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tab1;
    ------------------------------------------------------------------------------
    
    COL1   COL2
    ------ ----
    
      0 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tab2;
    ------------------------------------------------------------------------------
    
    COLY   COLZ
    ------ ----
    BLABLA -   
    -      A   
    
      2 record(s) selected.

Posting Permissions

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