DB Error on delete:: Key (id)=(n) is still referenced from table "reviews"

[clock509]

When I tried to delete a restaurant from 'Home.jsx', DB error occured with a message below:

ERROR: update or delete on table "restaurants" violates foreign key constraint "reviews_restaurant_id_fkey" on table "reviews"
DETAIL: Key (id)=(NUMBER) is still referenced from table "reviews".

So, when you create table 'reviews', you should add ON DELETE CASCADE at restaurant_id, otherwise the function DELETE doesn't work. So the implmented create table query is like as below:

CREATE TABLE reviews (
  id BIGSERIAL NOT NULL PRIMARY KEY,
  restaurant_id BIGINT REFERENCES restaurants(id) ON DELETE CASCADE NOT NULL,
  name VARCHAR(50) NOT NULL,
  review TEXT NOT NULL,
  rating INT NOT NULL check(rating >=1 AND rating <= 5)
);

[jbrian31]

If you have already created the table reviews with populated data and dont want to delete it, run the following SQL to alter it without dropping the table and re-adding it. Postgres requires you to drop a constraint on a foreign key, cant add the DELETE CASCADE, you must then readd the constraint.

ALTER TABLE reviews DROP CONSTRAINT reviews_restaurant_id_fkey, ADD CONSTRAINT reviews_restaurant_id_fkey FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE;

[yash-sharma01]

Thank you for this solution! @clock509 and @jbrian31