I wrote:
> Have the SQL committee simply failed to notice that in
> whacking this text around they changed the meaning? Which behavior is
> actually implemented by other RDBMSes?
If anyone is up for actually trying this, here is a script to test the
behavior in question:
create table pp (f1 int, f2 int, primary key (f1,f2));
create table cmssn (f1 int, f2 int, foreign key (f1,f2) references pp(f1,f2) on update set null);
create table cmfsn (f1 int, f2 int, foreign key (f1,f2) references pp(f1,f2) match full on update set null);
create table cmssd (f1 int default 0, f2 int default 0, foreign key (f1,f2) references pp(f1,f2) on update set
default);
create table cmfsd (f1 int default 0, f2 int default 0, foreign key (f1,f2) references pp(f1,f2) match full on update
setdefault);
insert into pp values (11, 22);
insert into pp values (11, 0);
insert into pp values (0, 0);
insert into cmssn values (11, 22);
insert into cmfsn values (11, 22);
insert into cmssd values (11, 22);
insert into cmfsd values (11, 22);
update pp set f2 = f2 + 1 where f2 > 0;
select * from cmssn;
select * from cmfsn;
select * from cmssd;
select * from cmfsd;
In Postgres this produces
f1 | f2
----+----11 |
(1 row)
f1 | f2
----+---- |
(1 row)
f1 | f2
----+----11 | 0
(1 row)
f1 | f2
----+---- 0 | 0
(1 row)
which shows that we are self-consistent but not actually compliant with
either old or new wordings of the spec :-(
The only other SQL DB I have handy is mysql 5.5.24, which shows up
pretty unimpressively: it gives a syntax error on the cmssd definition,
which would be all right because the manual says the innodb storage
engine doesn't support SET DEFAULT, except it *doesn't* give a syntax
error for creating cmfsd. Then, the update fails claiming that cmfsn's
FK constraint is violated, so they evidently don't implement that case
correctly. After removing cmfsn, the update fails again claiming that
cmfsd's FK constraint is violated, so yeah they are telling the truth
when they say SET DEFAULT doesn't work. The upshot is that only the
MATCH SIMPLE SET NULL case works at all in current mysql, and that
produces the result
mysql> select * from cmssn;
+------+------+
| f1 | f2 |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)
so they are nulling all the referencing columns in this case, which
matches the more recent specs but is clearly contrary to SQL92.
Anybody have DB2, or something else that might be thought to be pretty
close to spec-compliant?
regards, tom lane
