ON DELETE CASCADE is a foreign-key action that automatically removes dependent records when their referenced parent is deleted. It prevents invalid orphan records in hierarchical data. section 5.1
In an ACH file hierarchy, cascading deletion keeps related records valid:
- Deleting a batch header removes its entry, addenda, and batch control records. section 5.1
- Deleting a file control record removes the records associated with that file. section 5.1
- Foreign keys define the relationships on which the cascade operates. section 5.1
Listing 5.1 PlantUML definition of our database
@startuml #1
object ach_file_uploads { #2
ach_files_id: UUID #3
filename: VARCHAR(255) #3
file_hash: VARCHAR(32) #3
credit_total: NUMERIC(12,2) #3
debit_total: NUMERIC(12,2) #3
}
object ach_files {
ach_files_id: UUID
record_type: VARCHAR(1)
record_id: UUID
parsed: BOOLEAN
sequence: NUMERIC
unparsed_record: VARCHAR(94)
}
object ach_file_header_records {
record_id: UUID
fields for record type
}
object ach_batch_header_records {
record_id: UUID
file_header_id: UUID
fields for record type
}
object ach_entry_detail_ppd_records {
record_id: UUID
batch_header_id: UUID
fields for record type
}
object ach_addenda_ppd_records {
record_id: UUID
entry_detail_id: UUID
fields for record type
}
object ach_batch_control_records {
record_id: UUID
batch_header_id: UUID
fields for record type
}
object ach_file_control_records {
record_id: UUID
file_header_id: UUID
fields for record type
}
ach_file_uploads::ach_files_id <-- ach_files::ach_files_id #4
#4
ach_files::record_id <-- ach_file_header_records::record_id #4
ach_files::record_id <-- ach_batch_header_records::record_id #4
ach_files::record_id <-- ach_entry_detail_ppd_records::record_id #4
ach_files::record_id <-- ach_addenda_ppd_records::record_id #4
ach_files::record_id <-- ach_batch_control_records::record_id #4
ach_files::record_id <-- ach_file_control_records::record_id #4
#4
ach_batch_header_records::file_header_id ->
ach_file_header_records::record_id #4
ach_entry_detail_ppd_records::batch_header_id ->
ach_batch_header_records::record_id #4
ach_addenda_ppd_records::entry_detail_id ->
ach_entry_detail_ppd_records::record_id #4
ach_batch_control_records::batch_header_id -->
ach_batch_header_records::record_id #4
ach_file_control_records::file_header_id -->
ach_file_header_records::record_id #4
#4
@enduml #5
The database listing maps ACH files, batches, entries, and addenda through identifier fields, making the cascade relationships visible. section 5.1
