Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT?
От | Tom Lane |
---|---|
Тема | Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT? |
Дата | |
Msg-id | 10168.1339877928@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT? (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT?
(Dean Rasheed <dean.a.rasheed@gmail.com>)
Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT? ("Dickson S. Guedes" <listas@guedesoft.net>) Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Steve SingerДата:
Сообщение: Re: [RFC][PATCH] Logical Replication/BDR prototype and architecture
Следующее
От: Dean RasheedДата:
Сообщение: Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT?