Can't delete badge if a user has it as their title badge

[pfaffman (Jay Pfaffman)]

I guess this is another corrupt table issue? Though this is on an instance where it is unlikely that the database was shut down uncleanly.

I created and deleted a badge with no members and that worked fine. I added a user to a new badge and deleted it and that worked fine.

Started DELETE "/admin/badges/118" for 71.89.242.121 at 2020-05-28 16:50:04 +0000
Processing by Admin::BadgesController#destroy as */*
  Parameters: {"id"=>"118"}
  Rendering text template
  Rendered text template (Duration: 0.0ms | Allocations: 1)
Completed 500 Internal Server Error in 304ms (ActiveRecord: 0.0ms | Allocations: 114838)
ActiveRecord::InvalidForeignKey (PG::ForeignKeyViolation: ERROR:  update or delete on table "badges" violates foreign key constraint "fk_rails_38ea484ed4" on table "user_profiles"
DETAIL:  Key (id)=(118) is still referenced from table "user_profiles".
)
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/rack-mini-profiler-2.0.1/lib/patches/db/pg.rb:72:in `exec_params'
Failed to handle exception in exception app middleware : PG::ForeignKeyViolation: ERROR:  update or delete on table "badges" violates foreign key constraint "fk_rails_38ea484ed4" on table "user_profiles"
DETAIL:  Key (id)=(118) is still referenced from table "user_profiles".

But I don’t quite know where to look or what index to try to rebuild.

[RGJ (Richard - Communiteq)]

TABLE "user_profiles" CONSTRAINT "fk_rails_38ea484ed4" FOREIGN KEY (granted_title_badge_id) REFERENCES badges(id)

What does SELECT * FROM user_profiles where granted_title_badge_id = 118; tell you?

[pfaffman (Jay Pfaffman)]

That gives me two users.
Then I

delete  FROM user_profiles where granted_title_badge_id = 118;

and then I could delete the badge.

My problem appears to be solved, at least for this badge.

But shouldn’t deleting the badge do this? Now it seems more like a

[RGJ (Richard - Communiteq)]

@pfaffman

delete FROM user_profiles

you’ve just deleted the profiles of two users

Next time

UPDATE user_profiles SET granted_title_badge_id = (another id) WHERE granted_title_badge_id = 118

[pfaffman (Jay Pfaffman)]

Oh. My. That does seem like a problem. This is why I tell people not to make db changes in sql.

At least it was only 2 users. Maybe I can still scroll to see who they were.

But it still seems like a bug that you can’t delete a badge if someone has it.

Recategorizing as a bug.

[RGJ (Richard - Communiteq)]

@pfaffman

But it still seems like a bug that you can’t delete a badge if someone has it.

No. It seems like a (minor) bug that you can initiate the deletion of a badge if someone has it. It would be nicer if the user interface would prevent it. It is totally correct that the database prevents deletion and preserves referential integrity.

[pfaffman (Jay Pfaffman)]

Agreed that it’s better to not delete the badge than leave the system in a broken state, but right now it returns a 500 error with no explanation.

A minor UX bug, sure, but it keeps someone who doesn’t know as much as you do from being able to delete a badge.

Thanks very much for your help. I’m waiting to see what happens to those users whose profiles I deleted!