Claudine First of all, ALTER TABLE tablename ADD CASCADE never did give a confirmation message.
To see if it worked, you must LIST TABLE tablename - you should see a "FLAGS: CASCADE" near the top Second of all, when you add a CASCADE, you must be sure that there isn't another PK - FK relationship down the line that would be violated. For example, in CONCOMP, in Customer, CustID is the PK CustID is a foreign key in Contact and in TransMaster. So: ALTER TABLE Customer ADD CASCADE theoretically would mean that if you delete customer 100 from customer, all contacts for that customer AND transactions would be deleted as well. BUT, Transmaster has PK with the FK in TransDetail, so the detail from Transmaster would fail becuase of the PK on TransID detecting rows in TransDetail. To make this full cascade work, you must also ALTER TABLE TransMaster ADD CasCade That said, I tried the above and I confirm that there DOES seem to be a problem with cascade and forms. After the above two commands, I typed EDIT USING CustomerEdit WHERE CustID = 101 (101 has 2 rows in Contact, and one in TransMaster, with that TransMaster having one in TransDetail) I clicked on the DELETE button on the form, and the PK trap PREVENTED the delete - it shouldn't have. At the R> prompt, if I typed: DELETE ROW FROM Customer WHERE Custid = 101 It DID delete that row AND the 2 rows in Contact AND the 1 row in TransMaster AND the 1 row in TransDetail. Razzak, is this a bug which we should submit? David David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Claudine Robbins" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Tuesday, January 25, 2005 8:52 AM Subject: [RBG7-L] - Cascade > Hi list, > > I attempted to use the cascade functionality to automatically delete > children records when deleting the parent record. > > 01. I made sure that all tables had an FK pointing to the PK table field. > 02. I then typed alter table mytable add cascade. There was no system > confirmation (set mess on). > 03. I ENTERed a new form which was working fine beforehand and deleted the > current record (36688) SELECT (MAX(invoice_id)+1) INTO vinvoiceid INDICATOR > ivinvid FROM d_invoice_header. It appeared to work. > 04. I re-ENTERed my form again and again and every time I found a 0 in my > key field (instead of picking the next number from the main table) and it > locked RBase. > 05. A record had gotten written in the main table with a 0 in the PK field > 06. I tried to delete that record. It gave me an error msg that it couldn't > delete the record because of existing records in another table. There was > indeed a table with many 0's in the keyfield (I had forgotten about that, > this is a not so logical remnant of the Oracle db). However, wouldn't it > have been logical for all those records to be deleted if cascade was > operational? > 07. I dropped the FK association for that table. > 08. I re-ENTERed my form again with the same result. > 09. I typed alter table mytable drop cascade. Again no confirmation. > 10. I removed all the FK associations in the other tables. > 11. The db passed R:Scope scrutiny so I reloaded the database. > 12. The last 500 records in the main table went missing. > 13. I restored my db from a backup > 14. I don't think I'll be trying this again any time soon... > > I'm still curious however, does anyone have this set up successfully? > > Claudine > > P.S.: Sending 'warm' thoughts to everyone in the path of the winter > blizzard! > >
