CODEWITHSUNDEEP
django
Chris
Chris

Reputation: 89

Django: Adding FK relation to existing model

I have a project where I need to change a normal field to an FK field and that in multiple models. The problem is that the project is productive and the database (Postgres) is full of entries.

I started locally by just mindlessly changing the field type to forgein_key() and trying to do a migration. Seems that Django is so smart that he tries to update this field, but he stumbles because of values in the model that don't exist in the related model. How can I skip those errors? Can I tell Django to set the FK == null if the related entry does not exist?

Heres some code:

from persons.models import Customer, Employee

class Insurance(models.Model):
    id = models.BigAutoField(primary_key=True)
    customer = models.ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    employee_id = ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    objects = models.Manager()

Before those fields where char fields and the relation was made in the backend code. The reason I'm trying to do this is that I need the functionalities from Django like select / prefech_related for the search functionality in the frontend insurance table. The user should have the option to search also for the name of the insured person rather than just the ID.

This is the error caused by not existing entries:

django.db.utils.IntegrityError: insert or update on table "insurance_insurance" violates foreign key constraint "insurance_insur_customer_id_98e84761_fk_person_"
DETAIL:  Key (customer_id)=(100883) is not present in table "person_customer".

Can anyone tell me a solid safer way to make these relations?

Thanks in advance!

Upvotes: 1

Views: 745

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476547

You probably should do that before turning the IntegerField (?) into ForeignKey. Frst we can make the IntegerField nullable:

class Insurance(models.Model):
    id = models.BigAutoField(primary_key=True)
    customer = models.IntegerField(null=True, db_column='customer_id')
    employee_id = ForeignKey(Customer, models.SET_NULL, blank=True, null=True)

You can then make a migration with:

python3 manage.py makemigrations

In the data migration file [Django-doc], we can then run a query to set the values for which there are no customers to NULL:

from django.db.models import Q

def set_invalid_customers_to_null(apps, schema_editor):
    Customer = apps.get_model('app', 'Customer')
    Insurance = apps.get_model('app', 'Insurance')
    Insurance.objects.filter(
        ~Q(customer__in=Customer.objects.values_list('pk', flat=True))
    ).update(customer=None)

You can make an individual migration, or just add this migration function to the migration file constructed through the model change.

Next we can change the type of the column to a ForeignKey:

class Insurance(models.Model):
    id = models.BigAutoField(primary_key=True)
    customer = models.ForeignKey(Customer, null=True, db_column='customer_id')
    employee_id = ForeignKey(Customer, models.SET_NULL, blank=True, null=True)

Upvotes: 0

weAreStarsDust
weAreStarsDust

Reputation: 2742

You can follow next steps:

1) Make duplicate of fileds with FK, 

class Insurance(models.Model):
    id = models.BigAutoField(primary_key=True)
    customer = models.CharField()
    employee_id = models.CharField()
    customer_new = models.ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    employee_id_new = ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    objects = models.Manager()

2) Create script to migrate data from old fields to new fields as ForeignKey

3) Delete old fields 

class Insurance(models.Model):
    id = models.BigAutoField(primary_key=True)
    customer_new = models.ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    employee_id_new = ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    objects = models.Manager()

4) Rename new fields

class Insurance(models.Model):
    id = models.BigAutoField(primary_key=True)
    customer = models.ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    employee_id = ForeignKey(Customer, models.SET_NULL, blank=True, null=True)
    objects = models.Manager()

P.s.

Don't forget to make DB backup before each migration, to rollback in case of problems.

Upvotes: 2

Related Questions