Cascading Deletes
lazarus / 16-Databases

----------------------------------------------------------------------
fpcoder · 2014-08-13 23:28

Added a short example how to delete all detail records when the master
record is deleted.

http://wiki.freepascal.org/MasterDetail#Delete_all_Detail_records_if_M
aster_record_is_deleted

----------------------------------------------------------------------
mangakissa · 2014-08-15 07:47

QuoteThis is easy to do with Cascading Deletes. Once again, this is
for SQLite, but other systems work similarly. Can you explain this? If
I create a new table in firebird/MySQL when table exist, I get an
error messages. In your example you create a new table. Where are my
other datarows?

I really hate if people use Conn.ExecuteDirect instead of the proper
way using TSQLQuery.

----------------------------------------------------------------------
Never · 2014-08-15 10:05

[mangakissa]
kapibara instracts db to do the job via sqlite3 DDL
http://en.wikipedia.org/wiki/Data_definition_language


SQL Queries is part of DML
http://en.wikipedia.org/wiki/Data_manipulation_language

 http://www.sqlite.org/lang.html

----------------------------------------------------------------------
mangakissa · 2014-08-15 15:03

I'm sorry. Didn't read the keyword references which will indeed delete
all details before deleting the master. In most cases en foreign key
will be created manally.

----------------------------------------------------------------------
fpcoder · 2014-08-16 17:28

Hi mangakissa,

I recently learnt to use ExecuteDirect this way from the Delphi/Kylix
database development book. If you know about disadvantages compared to
Query.ExecSQL please tell.

Anyway, its striking how almost no code can achieve automatic deletes.
Except from enabling foreign keys, this is all 'extras' thats needed
for it to work:

Code: [Select]REFERENCES countries ON DELETE CASCADE
Not sure, but Postgres might need:

Code: [Select]REFERENCES countries(country_id) ON DELETE CASCADE
Attached demo shows it in practice.
Note: A small sample database is created automatically and the
database file is stored in directory returned by GetAppConfigDirUTF8
function. The directory is created if not existing. Supposed to be
crossplatform.

Scrolling in the countries grid updates the cities grid.
Type something in the country_name and city_name gridcells to add more
items and save to database.