Re: Referential constraint in On-Line 7

Jonathan Leffler wrote: > > Hi, > > I think I can both understand the confusion and explain it. > > I just did some testing with the attached script on a 7.20.UC1 OnLine > database (on a Sun Sparc 10 running Solaris 2.4). Using DB-Access in the > menu mode, it certainly appeared that the bug cited was found. I nearly > panicked! But before doing that, I checked what happened using my SQLCMD > program, and it seemed to work correctly -- the subsidiary row in table B > was deleted too. So I ran DB-Access in 'dbaccess dbase script' mode, and > it showed that the behaviour was correct -- no rows existed after the > delete. > > I went back to DB-Access in menu mode, and the line that appeared to show > that the cascaded delete did not work is revealed as an interloper; it is > actually the result of the first select from table B, not the second. > > Screen dump (multiple blank lines compressed to a single line): > > =========================================================================== > > SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit > Run the current SQL statements. > > ----------------------- apt@anubis_41 ---------- Press CTRL-W for Help -------- > > (constant) a0 a1 > > (constant) b0 b1 > > B values: 1 1 All done > > Table dropped. > > =========================================================================== > > I think that something like this may be the cause of your confusion. > If not, then you need to show how you demonstrate the problem, please. I think John missed my follow-up post, or my message didn't get through. The ON DELETE CASCADE foreign key constraint works very well if it is created by a script like "CREATE TABLE ..." OR "ALTER TABLE ...". BUT the dbaccess menu mode cannot turn the cascading delete on. In other words, the ON DELETE CASCADE constraint created through dbaccess menu mode doesn't work. I didn't try your example because I know it works. The bug is not in the engine but in dbaccess. To demonstrate the problem, let's do this: 1. create your two test table A with PK, B without any constraints; 2. start dbaccess, select Table --> Alter, select table B 3. select Constrain; 4. select Foreign -->Add 5. enter constraint name(whatever), referencing column(b0), referenced table(A), referenced column(a0); 6. at the prompt "ADD ENABLE CASCADE DELETE" select Yes, the CD column will change to Y 7. Exit and build new table, exit dbaccess. 8. insert your test record into table A, B 9. Try your DELETE FROM A WHERE 1=1; What happens? I am running Sun Sparc SunOS 5.5, Online 7.11 UC1. Thanks for your attention. Weimin Yang Aerotek Inc.