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!
