Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    29

    Unanswered: ON UPDATE RESTRICT v/s ON UPDATE NO ACTION

    Can anyone pleas explain me whats the difference between
    ON UPDATE RESTRICT and ON UPDATE NO ACTION clause of Foreign key constraint?

    Thanks in advance

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    check the doc for create table and foreign keys
    CREATE TABLE
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The difference is described in manual.
    CREATE TABLE - IBM DB2 9.7 for Linux, UNIX, and Windows

    But, it is not so easy to understans.


    ON UPDATE

    Specifies what action is to take place on the dependent tables when a row of the parent table is updated. The clause is optional. ON UPDATE NO ACTION is the default and ON UPDATE RESTRICT is the only alternative.

    The difference between NO ACTION and RESTRICT is described in the "Notes" section.

    Notes
    ...
    ...

    The use of NO ACTION or RESTRICT as delete or update rules for referential constraints determines when the constraint is enforced. A delete or update rule of RESTRICT is enforced before all other constraints, including those referential constraints with modifying rules such as CASCADE or SET NULL. A delete or update rule of NO ACTION is enforced after other referential constraints. One example where different behavior is evident involves the deletion of rows from a view that is defined as a UNION ALL of related tables.
    Code:
       Table T1 is a parent of table T3; delete rule as noted below.
       Table T2 is a parent of table T3; delete rule CASCADE.
       
       CREATE VIEW V1 AS SELECT * FROM T1 UNION ALL SELECT * FROM T2
    
       DELETE FROM V1
    If table T1 is a parent of table T3 with a delete rule of RESTRICT, a restrict violation will be raised (SQLSTATE 23001) if there are any child rows for parent keys of T1 in T3.

    If table T1 is a parent of table T3 with a delete rule of NO ACTION, the child rows may be deleted by the delete rule of CASCADE when deleting rows from T2 before the NO ACTION delete rule is enforced for the deletes from T1. If deletes from T2 did not result in deleting all child rows for parent keys of T1 in T3, then a constraint violation will be raised (SQLSTATE 23504).

    Note that the SQLSTATE returned is different depending on whether the delete or update rule is RESTRICT or NO ACTION.

  4. #4
    Join Date
    Jan 2011
    Posts
    29
    Thank you so much for Explanation.. I understood the concept

  5. #5
    Join Date
    Jan 2011
    Posts
    29
    Thanks for reply

Posting Permissions

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