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.
dBforums
>
Referential Integrity rules
04-11-07, 17:00
Linear Mode
