I'm trying to change a foreign key's constraint but I keep getting this error:
sqlalchemy.exc.OperationalError: (OperationalError) (1025, "Error on rename of './tillsammans_dev/profile_accommodation_contact' to './tillsammans_dev/#sql2-4c1-37' (errno: 152)") 'ALTER TABLE profile_accommodation_contact DROP FOREIGN KEY profile_accommodation_contact_ibfk_1' ()
I have been having problem with this before but whenever I got this error I would just wipe the entire database and rebuild it but I thought that I would try to learn how to make a proper migration.
My model before the update:
class ProfileAccommodationContact(CRUDMixin, db.Model):
__tablename__ = 'profile_accommodation_contact'
show_contact_info = db.Column(db.Boolean, nullable=True, default=False)
message = db.Column(db.String(254), nullable=True)
created = db.Column(db.BigInteger, nullable=False, default=time.time())
seen = db.Column(db.Boolean, nullable=True, default=False)
fk_from_user = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
r_from_user = db.relationship('User', backref='ProfileAccommodationContact', uselist=False, cascade='all', primaryjoin='User.id==ProfileAccommodationContact.fk_from_user')
fk_to_user = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
r_to_user = db.relationship('User', uselist=False, cascade='all', primaryjoin='User.id==ProfileAccommodationContact.fk_to_user')
fk_profile_accommodation = db.Column(db.Integer, db.ForeignKey('profile_accommodation.id'), nullable=False)
r_profile_accommodation = db.relationship('ProfileAccommodation', backref='ProfileAccommodationContact', uselist=False, cascade='all')
fk_contact_type = db.Column(db.Integer, db.ForeignKey('contact_type.id'), nullable=False)
r_contact_type = db.relationship('ContactType', backref='ProfileAccommodationContact', uselist=False, cascade="all")
How I want my model to look like in the database:
class ProfileAccommodationContact(CRUDMixin, db.Model):
__tablename__ = 'profile_accommodation_contact'
show_contact_info = db.Column(db.Boolean, nullable=True, default=False)
message = db.Column(db.String(254), nullable=True)
created = db.Column(db.BigInteger, nullable=False, default=time.time())
seen = db.Column(db.Boolean, nullable=True, default=False)
fk_from_user = db.Column(db.Integer, db.ForeignKey('user.uid'), nullable=True)
r_from_user = db.relationship('User', backref='ProfileAccommodationContact', uselist=False, cascade='all', primaryjoin='User.uid==ProfileAccommodationContact.fk_from_user')
fk_to_user = db.Column(db.Integer, db.ForeignKey('user.uid'), nullable=True)
r_to_user = db.relationship('User', uselist=False, cascade='all', primaryjoin='User.uid==ProfileAccommodationContact.fk_to_user')
fk_profile_accommodation = db.Column(db.Integer, db.ForeignKey('profile_accommodation.id'), nullable=False)
r_profile_accommodation = db.relationship('ProfileAccommodation', backref='ProfileAccommodationContact', uselist=False, cascade='all')
fk_contact_type = db.Column(db.Integer, db.ForeignKey('contact_type.id'), nullable=False)
r_contact_type = db.relationship('ContactType', backref='ProfileAccommodationContact', uselist=False, cascade="all")
The difference is on:
- fk_from_user
- r_from_user
- fk_to_user
- r_to_user
Edit: So my question is how I should edit the migration file so that I can migrate this change to the model? The file that flask-migration creates looks like this (it fails on the first op.drop_constraint):
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(u'profile_accommodation_contact_ibfk_1', 'profile_accommodation_contact', type_='foreignkey')
op.drop_constraint(u'profile_accommodation_contact_ibfk_2', 'profile_accommodation_contact', type_='foreignkey')
op.create_foreign_key(None, 'profile_accommodation_contact', 'user', ['fk_to_user'], ['uid'])
op.create_foreign_key(None, 'profile_accommodation_contact', 'user', ['fk_from_user'], ['uid'])
### end Alembic commands ###
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(None, 'profile_accommodation_contact', type_='foreignkey')
op.drop_constraint(None, 'profile_accommodation_contact', type_='foreignkey')
### end Alembic commands ###
