An orphan record is a database record whose required parent or related record no longer exists. In an ACH file, records form a hierarchy: file control, batch header, entry, and addenda records. Deleting a parent without handling its dependents leaves invalid data behind. section 5.1
Prevent orphan records by designing relationships deliberately:
- Define a foreign key from each dependent table to its parent table. section 5.1
- Use ON DELETE CASCADE when dependent records become invalid with their parent. section 5.1
- Apply primary keys, NOT NULL, UNIQUE, and DEFAULT constraints alongside appropriate data types. section 5.1
Deleting an ACH batch
If a batch header is deleted, its entry, addenda, and batch control records are no longer valid. A cascading foreign-key relationship deletes those dependent records with the batch, preventing orphans. Deleting a file control record similarly removes records associated with that file. section 5.1
