Topic: Delete (clear) Multiple Tables that are Connected with other Tables

Hi All, hope you guys can help or lead me to the right direction.

Now that I've built an application, I would like to share it with people at work. With one click at a hidden button, I could the necessary tables and clear the tables.  I've tried this code as a test in a testing environment.



procedure fmMainMenu_Button7_OnClick (Sender: TObject; var Cancel: boolean);
     begin
             SQLExecute('DELETE FROM dtTimeTableListing');
     end;


Yet I get the following message:

Error Message
FFOREIGN KEY constraint failed

Script: SQLExecute.

I am assuming the cause is links between other tables ? !

I've done as much research as my "PROGRAMMER WANT-TO-BE" brain can handle and would need simple coding to clear tables I think i need to before it becomes too spaghetti programming for my mind.

Also, am I on the right track to think, that one needs clear the links of secondary tables that feed to a main table ? (Does my sentence may any sense ?)

Thanks for your help everybody.

2 (edited by derek 2021-04-30 14:47:30)

Re: Delete (clear) Multiple Tables that are Connected with other Tables

Hi Jeff,
You're on the right lines.
You can't delete entries from a table if those entries are referenced in a related table (it would create 'orphan records');  it's what's known as 'referential integrity'.
So you need to delete the tables you want to clear in sequence, starting with the tables that have a relational dependency..
Have a look at the example - table2 is related to table1 and so must be deleted first (try it the other way around and you'll get the same error message you've been encountering).
(there is a copy of sqlite.db (sqlite2.db) in the attachment - it just saves you having to recreate test data when you're trying out the program).
Hope this helps,
Derek.

Post's attachments

Attachment icon deletetables2.zip 338.4 kb, 437 downloads since 2021-04-30 

Re: Delete (clear) Multiple Tables that are Connected with other Tables

Thanks, Derek, that was it.

So now I am going in the schema and looking at the linked tables and slowly scripting a cascading delete of each table before the last delete, which is the central database of clients.

I appreciate the help all give on the forum. Thanks.