D. Richard Hipp <d...@hwaci.com> wrote: > Consider the following SQL: > > CREATE TABLE t1(x integer); > INSERT INTO t1 VALUES(123); > CREATE TABLE t2(y integer REFERENCES t1 ON UPDATE SET NULL); > INSERT INTO t2 VALUES(123); > > UPDATE t1 SET x=x; -- key line: Is this considered an "update" > of t1.x? > > SELECT * FROM t2; -- did t2.y get nulled by the previous > statement? > > The question is this: Should the no-op UPDATE statement (x=x) cause > the ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or > not?
Apparently not. From SQL 92 (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt): 11.8p6 If an <update rule> is specified and a non-null value of a referenced column in the referenced table is updated to a value that is *distinct* from the current value of that column, then ... [Long descripton omitted. Emphasis mine.] Note: Otherwise, the <referential action> is not performed. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
