My Visual Database — Delete (clear) Multiple Tables that are Connected with other Tables

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

null@example.com (jeffmtl@hotmail.com) — Sat, 01 May 2021 10:00:32 +0000

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.

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

null@example.com (derek) — Fri, 30 Apr 2021 11:09:05 +0000

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.

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

null@example.com (jeffmtl@hotmail.com) — Fri, 30 Apr 2021 09:49:32 +0000

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.