Delete rows with foreign key in PostgreSQL Flask

Question

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'))

score 3 · Accepted Answer · edited May 23 '17 at 12:14

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.

it should be ALTER TABLE users ALTER COLUMN users_bestfriend_id DROP NOT NULL; and then 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; @ozgur thnx for ur help — LiLi, Feb 14 '15 at 10:45

Delete rows with foreign key in PostgreSQL Flask

1 Answers1