Re: Referential constraint in On-Line 7

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.