Results 1 to 7 of 7
  1. #1
    Join Date
    May 2011
    Posts
    15

    Unanswered: inserting with foreign keys

    hi all,

    I want to insert data into the child table before inserting into parent table.
    but I want to do this into the insert statement.
    Is there any option available with the insert statement.

    please guide me.......

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    You want an inconsistent database? Drop the FK and re-create when the parent rows are in-place.

  3. #3
    Join Date
    May 2011
    Posts
    15
    thanks for your reply..

    this solution i tried but i want to know if any option available with insert statement so that the constraints will be unchecked.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You are looking for deferrable constraints, but that is not supported by DB2

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You could define NOT ENFORCED foreign keys - they will be used by the optimizer but you will have to ensure integrity of data yourself, and there is a chance that your query results will not look as you would expect if you screw up.

    Check informational constraints in the manual.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, this example might not satisfy the OP's requirements,
    here is an example inserting child and inserting parent(if it was necessary) in a statement.
    (Insert into parent table was specified explicitly.)

    CREATE tables for test:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_fk_parent
    ( pk   INTEGER     NOT NULL PRIMARY KEY
    , desc VARCHAR(20)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_fk_child
    ( pk1  INTEGER     NOT NULL
    , pk2  INTEGER     NOT NULL
    , desc VARCHAR(30)
    , PRIMARY KEY(pk1 , pk2)
    , FOREIGN KEY(pk1)
        REFERENCES test_fk_parent(pk)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    INSERT first child row:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     insert_data(pk1 , pk2 , desc) AS (
    VALUES (100 , 1 , 'First child of a parent.')
    )
    , inter_result1 AS (
    SELECT *
     FROM  FINAL TABLE
           (INSERT INTO test_fk_parent
                 ( pk )
            SELECT DISTINCT
                   pk1
             FROM  insert_data n
             WHERE NOT EXISTS
                   (SELECT 0
                     FROM  test_fk_parent e
                     WHERE e.pk = n.pk1
                   )
           ) p
    )
    , inter_result2 AS (
    SELECT *
     FROM  FINAL TABLE
           (INSERT INTO test_fk_child
            SELECT *
             FROM  insert_data
           ) c
    )
    SELECT *
     FROM  inter_result1 r1
     RIGHT OUTER JOIN
           inter_result2 r2
       ON  r2.pk1 = r1.pk
    ;
    ------------------------------------------------------------------------------
    
    PK          DESC                 PK1         PK2         DESC                          
    ----------- -------------------- ----------- ----------- ------------------------------
            100 -                            100           1 First child of a parent.      
    
      1 record(s) selected.
    INSERT second child row:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     insert_data(pk1 , pk2 , desc) AS (
    VALUES (100 , 2 , 'Second child of a parent')
    )
    , inter_result1 AS (
    SELECT *
     FROM  FINAL TABLE
           (INSERT INTO test_fk_parent
                 ( pk )
            SELECT DISTINCT
                   pk1
             FROM  insert_data n
             WHERE NOT EXISTS
                   (SELECT 0
                     FROM  test_fk_parent e
                     WHERE e.pk = n.pk1
                   )
           ) p
    )
    , inter_result2 AS (
    SELECT *
     FROM  FINAL TABLE
           (INSERT INTO test_fk_child
            SELECT *
             FROM  insert_data
           ) c
    )
    SELECT *
     FROM  inter_result1 r1
     RIGHT OUTER JOIN
           inter_result2 r2
       ON  r2.pk1 = r1.pk
    ;
    ------------------------------------------------------------------------------
    
    PK          DESC                 PK1         PK2         DESC                          
    ----------- -------------------- ----------- ----------- ------------------------------
              - -                            100           2 Second child of a parent      
    
      1 record(s) selected.

    Result(rows in tables):
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_fk_parent;
    ------------------------------------------------------------------------------
    
    PK          DESC                
    ----------- --------------------
            100 -                   
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM test_fk_child;
    ------------------------------------------------------------------------------
    
    PK1         PK2         DESC                          
    ----------- ----------- ------------------------------
            100           1 First child of a parent.      
            100           2 Second child of a parent      
    
      2 record(s) selected.
    Last edited by tonkuma; 05-30-11 at 21:16. Reason: Minor edit of code. Add DISTINCT

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It's also possible to switch an existing constraint to be NOT ENFORCED and back.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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