PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL – On delete keep child data

Let’s assume that we have two tables users and messages with the following fields:

  • users: id, name, email
  • messages: id, content, user_id

I want to delete a user but keep all data in messages. Is there a way to do this?

Tags:

2

Answers


  1. 0

    If there is no foreign key constraint on messages.user_id, you can simply delete the data from users without affecting messages.

    In case of a foreign key constraint, simply set the desired messages.user_id to null first then delete the user.

    e.g in case of delete the user with id 5,

    update messages set user_id = null where user_id = 5;
delete from users where id = 5;
    Login or Signup to reply.
  2. 0

    Unlike what I see in the comments, you can keep the foreign key from messages to users (it is actually recommended you do keep it, precisely because it allows the user id to be automatically set to null when deleting a user). You also do not need to update the messages table first when you want to delete a user.

    The possible actions you can apply on your foreign key are described in the CREATE TABLE help page. What you want is create it with SET NULL.

    For instance and skipping the creation of users (and assuming users(id) corresponds to a login and is of type varchar):

    CREATE TABLE message (
    ID SERIAL PRIMARY KEY,
    User_ID VARCHAR REFERENCES Users(ID) ON DELETE SET NULL,
    Content VARCHAR NOT NULL
);

    As you can see, the foreign key constraint itself carries what should happen to the field when the corresponding record is deleted; if you delete a user, the DB will update the messages so that User_ID is set to NULL everywhere it needs to.

    delete from users where id = 'SomeLogin' is all you need.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search