A foreign key links a field in one table to unique data, usually a primary key, in another table. It expresses which records belong together and lets a relational database enforce referential integrity rather than leaving relationships to application code alone. section 5.1 section 5.12
In an ACH-processing system, records form a hierarchy: a file contains batches, batches contain entries, and entries can contain addenda. Foreign keys model those links so a child record cannot stand alone after its parent has disappeared. section 5.1 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
Inspect the table diagram to trace foreign-key fields through the ACH file hierarchy, from uploaded files to record types and related child records. section 5.1
A foreign key supports data integrity in several practical ways:
- Require a child value to reference an existing parent. An ach_records row, for example, needs an ach_files_id that exists in ach_files. section 5.6 section 5.7
- Prevent orphan or dangling records that otherwise require cleanup utilities and can contribute to incomplete-data failures. section 5.1 section 5.7
- Cascade deletions and updates when the relationship calls for it, so removing an ACH file also removes its associated records. section 5.6
- Allow optional relationships when only some child types apply, while retaining required links where every row needs a parent. section 8.4.1
Linking uploaded files to raw records
Create ach_files as the parent table, then give ach_records an ach_files_id column that references ach_files(ach_files_id). Mark it NOT NULL when every raw record must belong to a file, and use ON DELETE CASCADE and ON UPDATE CASCADE when associated records should follow changes to that file. section 5.6
Constraints change the surrounding workflow. Tests that insert child rows must first create valid parent rows, and refactoring table names or columns can expose failing inserts immediately. That extra setup is a tradeoff for detecting broken relationships early instead of repairing inconsistent production data later. section 5.6 section 5.7
Foreign keys alone do not guarantee that a hierarchy matches every deletion requirement. A design can still leave records behind if the relationships do not reflect the desired cleanup path, so model the lifecycle of files, batches, and entries before finalizing the schema. section 5.9 section 5.11
For exception tracking, one table can reference a required file, optional record types, and a lookup table of standard exception codes. This keeps exceptions connected to the relevant data while allowing fields that do not apply to remain NULL. section 8.4.1
