Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: Referential Integrity rules

    Hello, Have been reading the theory of DB2 Constraints for DB2 9. But I could not find any difference on the 2 update rules which is present namely Restrict and No Action.

    Can you pls help me to understand it practically.

    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check the SQL Reference Vol 2 for Create Table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    RESTRICT prevents an update if there are dependent records. NO ACTION performs the update and then checks if everything is fine afterwards. So NO ACTION could allow triggers to fire and fix things. Here is another example that fails with RESTRICT but succeeds with NO ACTION:
    Code:
    CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY );
    CREATE TABLE t2 ( b INT, FOREIGN KEY (b) REFERENCES t1 ON UPDATE NO ACTION );
    INSERT INTO t1 VALUES (2), (3), (4);
    INSERT INTO t2 VALUES (2), (3);
    UPDATE t1 SET a = a - 1;
    RESTRICT detects dependent rows and bails out. NO ACTION succeeds because all dependent rows in T2 still have a matching row it T1 after the UPDATE.
    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
  •