Referential integrity keeps related database records valid. In an ACH system, a file contains batches, entries, addenda, and control records; database relationships should preserve that hierarchy. It also guides column definitions, such as storing a one-character file modifier as VARCHAR(1) and monetary totals as NUMERIC(12,2). section 5.1
The central problem is orphaned data: deleting a batch header must not leave its entries, addenda, and batch control record without a valid parent. Likewise, deleting a file should remove the records belonging to it. Relational databases enforce these relationships with primary keys, foreign keys, and constraints, avoiding incomplete data that can cause maintenance work, crashes, or loops. section 5.1 section 5.12 section 5.7
Linking uploaded files to records
Make ach_records.ach_files_id a non-null foreign key that REFERENCES ach_files(ach_files_id). ON DELETE CASCADE removes associated records when an ACH file is deleted, and ON UPDATE CASCADE keeps the relationship aligned when its key changes. Each record insert must supply an ach_files_id that already exists. section 5.6 section 5.7
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
Browse the table layout to trace parent-child links from uploaded ACH files through headers, batches, entries, addenda, and controls before defining foreign keys. section 5.1
Implement integrity as part of database and test design:
- Define primary keys for records and foreign keys for every required parent-child relationship. section 5.12 section 5.7
- Choose deletion behavior deliberately; cascades can remove dependent records without separate cleanup code. section 5.6
- Update fixtures and unit tests so child records reference valid parent rows; constraint failures reveal incomplete setup or outdated SQL. section 5.6 section 5.7
- Treat late adoption as rework affecting code and tests, rather than deferring it to manual programs that scan for missing relationships. section 5.11 section 5.12
