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

Postgresql – SQL not allowing deletion of related data

In SQL I have:

        CREATE TABLE Customer(
            cust_id INT NOT NULL PRIMARY KEY CHECK (cust_id > 0),
            full_name TEXT NOT NULL,
            phone TEXT NOT NULL,
            address TEXT NOT NULL CHECK (LENGTH(address) >= 3)
        );

        CREATE TABLE "Order"(
            order_id INT NOT NULL PRIMARY KEY CHECK (order_id > 0),
            date TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL
        );

        CREATE TABLE Dish(
            dish_id INT NOT NULL PRIMARY KEY CHECK (dish_id > 0),
            name TEXT NOT NULL CHECK (LENGTH(name) >= 3),
            price NUMERIC NOT NULL CHECK (price > 0),
            is_active BOOLEAN NOT NULL
        );

        CREATE TABLE Placed(
            order_id INT NOT NULL PRIMARY KEY CHECK (order_id > 0),
            cust_id INT NOT NULL CHECK (cust_id > 0),
            FOREIGN KEY (order_id) REFERENCES "Order" (order_id) ON DELETE CASCADE,
            FOREIGN KEY (cust_id) REFERENCES Customer (cust_id)
        );

When trying to delete a customer I want to delete his personal data but to keep his placed orders and not to delete them.

When trying to run:

DELETE FROM Customer WHERE cust_id = 1

I get:

update or delete on table "customer" violates foreign key constraint "placed_cust_id_fkey" on table "placed"
DETAIL:  Key (cust_id)=(1) is still referenced from table "placed".

How can I fix this?

Tags:

2

Answers


  1. 0

    You can not (and should not) delete customers from database, because the Placed table linked to Customer by foreign key. I can to advice you SOFT DELETE pattern in this case. It demand to add deleted_at column on Customer table and using this column you can mark a customer as deleted intead delete it from database.

    CREATE TABLE Customer(
    cust_id INT NOT NULL PRIMARY KEY CHECK (cust_id > 0),
    full_name TEXT NOT NULL,
    phone TEXT NOT NULL,
    address TEXT NOT NULL CHECK (LENGTH(address) >= 3),
    deleted_at timestamp
);
UPDATE Customer SET deleted_at = current_timestamp WHERE cust_id = 1;

    You can try this concept here

    Login or Signup to reply.
  2. 0

    Do it with two Commands.

    UPDATE placed SET cust_id = NULL WHERE cust_id = 1;
DELETE FROM Customer WHERE cust_id = 1;

    Soft deliting is a clean way, but because of the laws for example in Europe, you need to hard delete it.

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

Back To Top
Search