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?
PostgreSQL says "no" - the t2.y value is not nulled unless the t1.x
value really does change values.
Microsoft SQL Server says "yes" - the t2.y value is nulled if t1.x is
written, even if it is overwritten with an identical value.
Oracle says: "I don't know how to do ON UPDATE SET NULL".
And a related question: Does anybody really care about ON UPDATE SET
NULL? Has anybody ever actually seen ON UPDATE SET NULL used in
practice?
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
