Database Migration Foreign Key Constraint Issues

[jerryagenyi-sul]

Problem

Several database migration files had foreign key constraints referencing the organizations table before it was created, causing migration failures.

Root Cause

Migration file naming caused incorrect execution order:

• 2025_10_05_235959_create_families_table.php (runs early)
• 2025_10_06_000000_create_members_table.php (runs early)
• 2025_10_06_000001_create_member_attributes_table.php (runs early)
• 2025_10_06_000003_create_badge_types_table.php (runs early)
• 2025_10_06_135521_create_organizations_table.php (runs late)

Solution Implemented

✅ RESOLVED: Fixed by removing foreign key constraints from early migrations and adding them in a separate migration after all tables exist.

Files Modified:

• backend/database/migrations/2025_10_05_235959_create_families_table.php
• backend/database/migrations/2025_10_06_000000_create_members_table.php
• backend/database/migrations/2025_10_06_000001_create_member_attributes_table.php
• backend/database/migrations/2025_10_06_000003_create_badge_types_table.php
• backend/database/migrations/2025_10_09_144944_add_foreign_keys_after_all_tables.php

Changes Made:

1. Replaced foreignId('organization_id')->constrained() with unsignedBigInteger('organization_id')
2. Added foreign key constraints in final migration after all tables exist
3. Tested migrations successfully

Testing

php artisan migrate:reset
php artisan migrate

✅ All migrations now run successfully without errors.

Prevention

For future migrations:

1. Ensure proper naming/ordering of migration files
2. Consider dependencies when creating foreign key constraints
3. Use separate migration for foreign keys if table order is uncertain

---

Status: ✅ RESOLVED
Priority: High (Database Integrity)
Labels: bug, database, migrations