How do I keep data B on a child table that is connected to data A on the parent table, while deleting data A on the parent table?

I’m creating a forum-type web app where users can leave comments based on a specific topic. There are registration and login steps. Right now, when a user account is deleted, everything tied to the user is also deleted, such as the comments.

Is there a way to keep the user-generated comments, but delete the user account? And replace the username with “deleted”? Do I simply remove ON DELETE CASCADE from the migration step when creating the comments table?

CREATE TABLE users (
  id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  username TEXT NOT NULL UNIQUE,
  nickname TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  date_created TIMESTAMPTZ DEFAULT now() NOT NULL
);

CREATE TABLE comments (
  id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  comment TEXT NOT NULL,
  date_created TIMESTAMPTZ DEFAULT now() NOT NULL,
  date_modified TIMESTAMPTZ DEFAULT now() NOT NULL,
  vinmake_id INTEGER
    REFERENCES vinmake(id) ON DELETE CASCADE,
  dtc_id INTEGER
    REFERENCES dtc(id) ON DELETE CASCADE NOT NULL,
  user_id INTEGER
    REFERENCES users(id) ON DELETE CASCADE NOT NULL
)

Thank you.

Answer

One option would be to change the foreign key definition to on delete set null instead of on delete cascade – this also requires making the column nullable (which it currently isn’t):

user_id INTEGER REFERENCES users(id) ON DELETE SET NULL

As explained in the documentation, this option causes the referencing column(s) in the referencing row(s) to be set to nulls […], when the referenced row is deleted.

Side note: in general, I would not actually recommend deleting users from your referential table. Instead, you could have another column in table users that indicate that the user was deleted (say, a boolean column called active), and that you could use as a filter in your queries. This is safer in some ways, and avoids the need to play around with the foreign key later on.