How to Fix Django Migration Errors: UUID vs. BigInt Foreign Key Conflicts

When working with Django models, you might encounter foreign key constraint errors due to mismatched ID types—typically between UUID and bigint (PostgreSQL’s default for auto-increment IDs). This guide provides a step-by-step solution to resolve these issues, especially when dealing with missing tables or inconsistent migrations.

---

🔍 Understanding the Error

The error:

psycopg2.errors.DatatypeMismatch: foreign key constraint "..." cannot be implemented  
DETAIL: Key columns "fundraiser_id" and "id" are of incompatible types: uuid and bigint.  

occurs when:

• A model (Fundraiser) uses UUIDField as its primary key.
• Another model (Payment) tries to reference it with a bigint foreign key.
• The database enforces strict type matching for foreign keys.

---

🎯 Final Thoughts

This guide ensures:

✔ Clean database schema with correct UUID foreign keys

✔ No data loss (when following backup steps)

✔ Prevents future migration conflicts

Since the fundraiser migrations aren't applying but you're still getting foreign key errors, we need to force a complete reset. Here's the step-by-step solution:

1. Full Reset Process

# 1. Backup schema (just in case)
/var/www/tunaresq_be/venv/bin/python manage.py dumpdata --exclude=contenttypes --exclude=auth.Permission > schema_backup.json

# 2. Drop all problematic tables
/var/www/tunaresq_be/venv/bin/python manage.py dbshell <<EOF
DROP TABLE IF EXISTS common_fundraisingcategory CASCADE;
DROP TABLE IF EXISTS payment_paymentmethodfieldconfig CASCADE;
DROP TABLE IF EXISTS fundraiser_fundraiser CASCADE;
\q
EOF

# 3. Reset all migration history
/var/www/tunaresq_be/venv/bin/python manage.py migrate common zero --fake
/var/www/tunaresq_be/venv/bin/python manage.py migrate fundraiser zero --fake
/var/www/tunaresq_be/venv/bin/python manage.py migrate payment zero --fake

# 4. Delete migration files
find /var/www/tunaresq_be/common/migrations/ -name "000*.py" ! -name "__init__.py" -delete
find /var/www/tunaresq_be/fundraiser/migrations/ -name "000*.py" ! -name "__init__.py" -delete
find /var/www/tunaresq_be/payment/migrations/ -name "000*.py" ! -name "__init__.py" -delete

# 5. Regenerate fresh migrations
/var/www/tunaresq_be/venv/bin/python manage.py makemigrations

2. Apply Migrations in Correct Order

# 0. Create common tables
/var/www/tunaresq_be/venv/bin/python manage.py migrate common

# 1. First create fundraiser tables
/var/www/tunaresq_be/venv/bin/python manage.py migrate fundraiser

# 2. Then create payment tables with proper UUID foreign keys
/var/www/tunaresq_be/venv/bin/python manage.py migrate payment

3. Verification

# Check tables exist with correct types
/var/www/tunaresq_be/venv/bin/python manage.py dbshell <<EOF
\d common_fundraisingcategory
\d fundraiser_fundraiser
\d payment_paymentmethodfieldconfig
\q
EOF

# Should show:
#common_fundraisingcategory -> uuid
# fundraiser_fundraiser.id -> uuid
# payment_paymentmethodfieldconfig.fundraiser_id -> uuid (foreign key)

Key Notes:

1. The --fake reset ensures Django's migration history clears without touching the database
2. Deleting migration files forces Django to regenerate them with current model definitions
3. Applying migrations in order (fundraiser first) prevents foreign key errors

If Errors Persist:

1. Check your models:

   # fundraiser/models.py
   class Fundraiser(models.Model):
       id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
       # ...

   # payment/models.py
   class PaymentMethodFieldConfig(models.Model):
       fundraiser = models.ForeignKey(
           'fundraiser.Fundraiser',
           on_delete=models.CASCADE,
           to_field='id'  # Explicit UUID reference
       )

1. Manually inspect generated migrations before applying:

   cat /var/www/tunaresq_be/fundraiser/migrations/0001_initial.py
   cat /var/www/tunaresq_be/payment/migrations/0001_initial.py

This nuclear option guarantees clean tables with proper UUID relationships. Let me know if you need help interpreting the generated migration files.

Final Thought

If you still face issues, check:

• Model definitions (UUIDField vs AutoField)
• Migration dependencies
• Database engine compatibility

Comments

[Vincent Tommi]

I appreciate for pointing this issue out