CODEWITHSUNDEEP
sqldjangomigrationforeign-keysconstraints
phoxley
phoxley

Reputation: 474

ForeignKey Relationships and Migrating Database in Django

I'm working on a django project with these two (exemplary) models:

Tenant: id, Building(ForeignKey), User(ForeignKey), NameOfTenant(CharacterField)

Building: id, Address(CharacterField), DateWhenItWasBuild(Date)

What I am basically now planning to do is to allow a building to have multiple apartments and to associate a tenant with an apartment rather than a building. Hence, I am now planning to migrate to the following three models:

Tenant: id, Aparment(ForeignKey), User(ForeignKey), NameOfTenant(CharacterField)

Apartment: id, Building(ForeignKey), RoomNumber(Interger), Address(CharacterField)

Building: id, DateWhenItWasBuild(Date)

First, I added the apartment model / table and populated it with the information from the building table (address and building foreign key). Second, I added the foreign key field for the apartment to the tenant model.

If I now want to reference the apartment id in the tenant table to the apartment (which in turn references to a building), I get a foreign key constraint exception:

(1452, 'Cannot add or update a child row: a foreign key constraint fails
(`database`.`tenant_tenant`, CONSTRAINT `apartment_id_refs_id_5dfbfc78bb68defd`
FOREIGN KEY (`apartment_id`) REFERENCES `property_apartment` (`id`))')

I am not quite sure why this is happening but I suspect that it the following might be causing the issues: Tentant has a foreign key to Building and a foreign key to apartment. However, apartment has a foreign key to building as well.

In the next step, I would have added the reference in tenant to the apartment and then deleted the reference to the building. The problem here is that I can't delete the building reference first and then add the apartment reference because I would lose the information in which building/apartment a tenant is living in.

Does anyone have an idea if this is the problem or if I am just missing something entirely different?

Upvotes: 0

Views: 520

Answers (1)

dani herrera
dani herrera

Reputation: 51665

First approach:

You can create tree new tables and when migration is finished rename tables to original name:

Create table new_building ( id, DateWhenItWasBuild(Date) );
insert into new_building ( id, DateWhenItWasBuil ) 
select ( id, DateWhenItWasBuil )  from building;

Create table Apartment (id, Building(ForeignKey), 
RoomNumber(Interger), Address(CharacterField) ;

insert into Apartment (id, Building(ForeignKey), 
RoomNumber(Interger), Address(CharacterField) )
select id, id, NULL, Address from building;

create table new_Tenant( id, Aparment(ForeignKey), 
User(ForeignKey), NameOfTenant(CharacterField) );

insert into new_Tenant ( id, Aparment, 
User, NameOfTenant ) 
select id, building, user, nameofTenant)

Then drop old tables and rename news:

DROP TABEL Tenant;
DROP TABLE building;
RENAME TABLE new_Tenant TO Tenant
    , new_building TO building;

I assume that at this point apparmentId is buildingId. But you can take this value from another table.

Remember that is not mandatory to make by hand the create table. You can rename table name in meta and create tables with syncdb:

class Tenant(models.Model):    
    # ...
    class Meta:
        db_Table = 'new_tenant'

Also, you can drop foreign key constraint with out delete foreign key field. 

[ALTER TABLE tenant_tenant DROP FOREIGN KEY apartment_id_refs_id_5dfbfc78bb68defd;][2]

Remember that south can help to you in this issue.

Upvotes: 1

Related Questions