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

Postgresql – How to properly delete data from a table and chain the deletion

I’m new to postgreSQL and I’m trying to delete a row from a table and then also delete the relevant data from another table and then the linking table; Im trying to learn while doing so im pretty sure what im doing is way wrong.

Its giving me an error:

update or delete on table "users" violates foreign key constraint "usertopantry_user_id_fkey" on table "usertopantry"

The userstopantry table looks like:

user_id : integer : NOT NULL true : Primary KEY: true DEFAULT nextval('usertopantry_user_id_seq'::regclass)

pantry_id: integer : NOT NULL true : Primary KEY: true DEFAULT 
nextval('usertopantry_pantry_id_seq'::regclass)

This is the code im running to try and DELETE from all sources when a user DELETES a user:

const deleteUser = (request, response) => {
  const id = parseInt(request.params.id)

  pool.query('DELETE FROM users WHERE user_id = $1', [id], (error, userResults) => {
    if (error) {
      throw error
    }
    if(response) {
      pool.query('SELECT pantry_id FROM userstopantry WHERE user_id = $1', [id], (error, pantryResults) => {
        if (error) {
          throw error
        }

        pool.query('DELETE FROM pantries WHERE pantry_id = $1', [pantryResults.rows[0].pantry_id], (error, deletionResults) => {
          if (error) {
            throw error
          }
          pool.query('DELETE FROM userstopantry WHERE user_id = $1', [id], (error, results) => {
            if (error) {
              throw error
            }
            response.status(200).send(`User deleted with ID: ${id}`)
          })
        })
      })
    }
  })
}

Im using Node.js with pg.pool

Tags:

2

Answers


  1. 0

    I believe DELETE CASCASE is exactly what you’re looking for. It will delete related rows in other tables automatically.

    This is a good first article https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-delete-cascade/

    Login or Signup to reply.
  2. 0

    The error you’re encountering is due to a foreign key constraint in PostgreSQL that prevents you from deleting a user while there are still references to that user in the userstopantry table. To resolve this issue, you need to delete the related rows from the userstopantry table first before deleting the user.
    `const deleteUser = (request, response) => {
    const id = parseInt(request.params.id)

    // Start a transaction
    pool.query(‘BEGIN’, (error) => {
    if (error) throw error;

    // First, delete from userstopantry to avoid the foreign key constraint violation
pool.query('DELETE FROM userstopantry WHERE user_id = $1 RETURNING pantry_id', [id], (error, pantryResults) => {
  if (error) {
    pool.query('ROLLBACK', () => {
      throw error;
    });
  }

  // Delete the related pantries using the returned pantry_ids (if needed)
  if (pantryResults.rows.length > 0) {
    const pantryIds = pantryResults.rows.map(row => row.pantry_id);

    pool.query('DELETE FROM pantries WHERE pantry_id = ANY($1)', [pantryIds], (error) => {
      if (error) {
        pool.query('ROLLBACK', () => {
          throw error;
        });
      }

      // After the related records are deleted, delete the user
      pool.query('DELETE FROM users WHERE user_id = $1', [id], (error) => {
        if (error) {
          pool.query('ROLLBACK', () => {
            throw error;
          });
        }

        // Commit the transaction if everything is successful
        pool.query('COMMIT', (error) => {
          if (error) {
            pool.query('ROLLBACK', () => {
              throw error;
            });
          }

          response.status(200).send(`User deleted with ID: ${id}`);
        });
      });
    });
  } else {
    // If no pantries to delete, just delete the user
    pool.query('DELETE FROM users WHERE user_id = $1', [id], (error) => {
      if (error) {
        pool.query('ROLLBACK', () => {
          throw error;
        });
      }

      pool.query('COMMIT', (error) => {
        if (error) {
          pool.query('ROLLBACK', () => {
            throw error;
          });
        }

        response.status(200).send(`User deleted with ID: ${id}`);
      });
    });
  }
});

    });
    };
    `

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

Back To Top
Search