I maintain a Django app with a postgresql backend. Users log in and exchange messages (forum style) with one another.

All users are identified by unique usernames (saved in the auth_user table), that also double up as nicknames for the users when they interact with one another in the forum.

I want to delete/deprecate data of users who haven't logged a session in my web app since the past 3 months. I'm doing this in order to free up nicknames for new users. I.e. new users need to keep a nickname, and if one's taken, they are made to retry. So if I can get rid of old users, the average number of retries a new user has to do will go down too. I.e. it'll become easier for incoming newbies to register for my service. So that's my goal here.

Ideally, I would simply delete the required rows from auth_user, however there are foreign key constraints that get violated (since ON DELETE CASCADE is not set on them, most are DEFERRABLE INITIALLY DEFERRED).

I was going to edit all these foreign key constraints and change them to ON DELETE CASCADE. But before I do that, I wanted to get expert advice on whether that's my only option in the scenario I've described. I'll have to make changes to foreign keys in ~50 tables, and secondly, ON DELETE CASCADE feels a bit too aggressive.

  • Just a reflection, if you remove their messages conversations they participated in might look weird. – Lennart Aug 19 '16 at 13:11
  • @Lennart: True, what's an alternative strategy you would suggest? – Hassan Baig Aug 19 '16 at 13:43
  • Can't you just update the nicknames to NULL or like stackoverflow, to a sequence-driven anonymous name? – Daniel Vérité Aug 19 '16 at 14:17
  • @DanielVérité: that's not a bad idea. You could elaborate that with an example and just write it as an answer too, since in my case, it's a viable alternative. – Hassan Baig Aug 22 '16 at 14:54

Create an SQL that does the same as the ON DELETE CASCADE would do. Remove the rows from the corresponding ~50 tables in the right order before you remove the users. This is some work but it keeps your data safe from an application error that does an accidental remove of a user with (lots of) messages.

| improve this answer | |
  • 'mimics' as in? – Hassan Baig Aug 19 '16 at 13:48
  • acts like. does the same thing. – Marco Aug 19 '16 at 14:13
  • So you literally meant SQL that does it by brute force – Hassan Baig Aug 22 '16 at 14:53
  • I meant an SQL script that does the same thing as the ON DELETE CASCADE would do. – Marco Aug 23 '16 at 6:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.