[EMAIL PROTECTED] 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/> ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
