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
1
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}`);
});
});
}
});
});
};
`
1
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/