CODEWITHSUNDEEP
pythondatabasepostgresqlflaskflask-migrate
LiLi
LiLi

Reputation: 301

Delete rows with foreign key in PostgreSQL Flask

I want to delete a row from my model which has ForeignKey with postgresql. But I got this error:

IntegrityError: (IntegrityError) update or delete on table "users" violates foreign key constraint "users_bestfriend_id_fkey" on table "users" DETAIL: Key (id)=(3) is still referenced from table "users". 'DELETE FROM users WHERE users.id = %(id)s' {'id': 3}

I think to solve it I should use CASCADE but don't know how to use it in my model so thanks if u help :)

model.py:

friends = db.Table('friends',
db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
db.Column('friend_id', db.Integer, db.ForeignKey('users.id'))
)

class Users(db.Model):
   id = db.Column(db.Integer, primary_key = True)
   name = db.Column(db.String(50), index=True)
   age= db.Column(db.Integer )
   email = db.Column(db.String(50),index=True, unique= True)
   bestfriend_id = db.Column(db.Integer, db.ForeignKey('users.id'))


   is_bestfriend = db.relationship( 'Users', uselist=False, remote_side=[id], post_update=True)

view.py:

# delete user profile
@layout.route('/delete/<int:id>')
@login_required
def delete(id):
  user = Users.query.get_or_404(id)
  if g.user.id == user.id:
           flash('You are not allow to delete yourself.')

  else:
      db.session.delete(user)
      db.session.commit()
      flash('delete done.')
  return redirect(url_for('layout.user', id=id, page=1, sortby='normal'))

Upvotes: 5

Views: 3843

Answers (1)

Ozgur Vatansever
Ozgur Vatansever

Reputation: 52133

The documentation is pretty clear about how to specify integrity constraints for columns in SQLAlchemy.

You should specify ondelete constraint as SET NULL for your bestfriend_id attribute, so when a user is deleted from table, his friends shouldn't get deleted along with him.

bestfriend_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)

I don't know if Flask automatically alters the column when you made a change on that so I think you should also update the column manually.

ALTER TABLE users ALTER COLUMN users_bestfriend_id DROP NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_bestfriend_id_fkey, ADD CONSTRAINT users_bestfriend_id_fkey FOREIGN KEY (bestfriend_id) REFERENCES users(id) ON DELETE SET NULL;

You want want to look at this SO question or the documentation for further information about types of referential integrity constraints.

Hope this helps.

Upvotes: 3

Related Questions