How to delete rows that have valid referencing keys in PostgreSQL

Question

I have two tables in my database named user and account. The user table has a foreign key column named account_id which is bound to the id column of table account.

More specifically, the simplified database structure is as follows:

CREATE TABLE account (
            id INTEGER PRIMARY KEY,
            name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE "user" (
            id INTEGER PRIMARY KEY,
            login VARCHAR NOT NULL UNIQUE,
            pass VARCHAR NOT NULL,
            account_id INTEGER  NOT NULL,
            CONSTRAINT account_fk FOREIGN KEY (account_id)
      REFERENCES account (id) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE
);

I'd like to know whether it is possible to delete a row of table user without also deleting the account being referenced by the specific record?

Whenever I try to delete an account, the related rows in table user are deleted as well (due to the ON DELETE CASCADE clause). However, I need the same series of events taking place in the other direction as well.

What should happen if two or more users refer to the same account.id ? — wildplasser
– wildplasser, Commented Feb 12, 2014 at 9:33
If you really have a one-to-one relation, you could combine the two tables into one. No additional logic needed! — wildplasser
– wildplasser, Commented Feb 13, 2014 at 19:26

Roman-Stop RU aggression in UA · Accepted Answer · 2014-02-12 09:25:59Z

1

Use trigger for that

CREATE OR REPLACE FUNCTION delete_account() RETURNS trigger AS
$$
BEGIN
    DELETE FROM account WHERE id = OLD.account_id;
END
$$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER trig_delete_account
  AFTER DELETE ON "user"
    FOR EACH ROW
EXECUTE PROCEDURE delete_account();

But keep in mind that other users referencing the same account will be also deleted. You probably have one-to-one relation and need unique key on user.account_id to enforce that.

answered Feb 12, 2014 at 9:25

Roman-Stop RU aggression in UA

16k3 gold badges52 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

irko Over a year ago

Thank you for reply. But our DB was changed so question became not actual :) But I will keep it in my mind!

Collectives™ on Stack Overflow

How to delete rows that have valid referencing keys in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related