Re: Referential constraint in On-Line 7

Jonathan I agree with you here. I am glad you explained it!!!!!! ------------------------------------------------------------------------- Cheryl Kendricks Internet:cherylk@prod1.jcdc.doleta.gov OR cherylk@gwysmtp.jcdc.doleta.gov DTSI, Inc. Database Administrator - DOL Job Corps San Marcos, Texas ------------------------------------------------------------------------ On Fri, 14 Jun 1996, Jonathan Leffler wrote: } Hi, } } I did not receive your follow-up post -- I don't think I've seen any such } message other than the one to which I'm responding now. } } OK, the problem is wholly within DB-Access. } } I'm tempted to say "Why are you using interactive schema editing within } DB-Access, especially if it is important enough to warrant a foreign key } definition?" You are going to need the schema for the table again at some } time, so why not start out with it now? Or, put another way, I never use } the Table option of DB-Access if I can avoid it, and except in cases such } as this I can avoid it 100% of the time... } } Yes, there is a problem if you create a foreign key on a column which you } also create an index on. I created a table b with column b0 having a } non-unique index on it, and then added a foreign key on column b0, and } DB-Access failed to create the foreign key, producing error -350 (index } already exists on column). I dropped the index from the column and tried } to rebuild the table, but the table already existed. I had to discard the } new table, drop table b which had been created, then recreate table b, this } time with no index on column b0 but with the same FK specification, and } behold, it worked. } } Since the database I was using has transactions, there are a couple of bugs } here: } } 1. The whole table creation process should have been in a transaction } which is rolled back if any part of it fails -- this would mean that I } didn't have to lose the laboriously typed in table schema. Even if the } database didn't have transactions, it would be courteous if DB-Access } backed out the parts of the build which worked when the overall operation } fails, especially as it does not do any serious checking prior to sending } the table creation/alteration statements to the engine. } } 2. The foreign key creation process requires that there is no index on the } column on which you create the FK. I imagine that there are similar } problems with primary keys and on tables where the primary key is also } a foreign key -- that requires some checking, which is painful since it } involves manually doing the b****y tests. } } Short answer -- don't use the Table option of DB-Access to create tables. } Create the SQL statement yourself, put the definition under source code } control (you do use such a system, don't you?), and build the table using } that. } } Long Answer -- there be bugs in the way the Table option of DB-Access } creates foreign keys (and probably primary keys too). And arguably, } there are problems with the way ALTER TABLE behaves when you add a } FK or PK constraint after the table is created with indexes of some } sort on the FK/PK columns. } } Yours, } Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> } } >Date: Fri, 14 Jun 1996 15:51:09 -0400 } >From: wyang <wyang@aerotek.com> } > } >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 Constraint; } >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. } }