From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: Possible to delete record from all tables at the same time?
Date: Mon, 26 Sep 2005 14:21:35 -0400
Message-ID: <60oe6flmfk.fsf@dba2.int.libertyrms.com> (raw)
References: <1127754749.860478.171440@g47g2000cwa.googlegroups.com>
mwsenecal@yahoo.com writes:
> Is it possible to delete a record from all tables in the database at
> the same time, without having to execute a separate "DELETE" statement
> for each table?
>
> I have a situation where I need to delete user records from our system.
> The user account information is spread across a handful of tables. Each
> table has an "id" field that contains the user's id, so the tables do
> have one field in common. When purging a user account from the system,
> I'd like to be able to issue one command that will delete the user
> account information from all tables, rather than issue separate delete
> commands for each table, something along the lines of:
>
> DELETE FROM ALL_TABLES WHERE userId = "whatever";
>
> Is this possible?
Yes, it is, though not via that mechanism.
<http://www.postgresql.org/docs/current/static/ddl-constraints.html;
This would be handled via a set of foreign keys of the "ON DELETE
CASCADE" sort.
Thus, you'd have one central "user" account, with the id field.
A table associating users with privileges might look like the
following:
CREATE TABLE user_privileges (
privilege_no integer REFERENCES privileges ON DELETE RESTRICT,
user_id integer REFERENCES user(id) ON DELETE CASCADE,
primary key (privilege_no, user_id)
);
Other tables would similarly reference "user(id) ON DELETE CASCADE";
whenever you delete from table user, the corresponding entries in
those tables would automatically be deleted.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/lisp.html
Rules of the Evil Overlord #100. "Finally, to keep my subjects
permanently locked in a mindless trance, I will provide each of them
with free unlimited Internet access. <http://www.eviloverlord.com/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: Possible to delete record from all tables at the same time?
In-Reply-To: <60oe6flmfk.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox