X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 81804D1B1A0 for ; Thu, 1 Jul 2004 07:27:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32914-09 for ; Thu, 1 Jul 2004 10:27:42 +0000 (GMT) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id 09776D1B170 for ; Thu, 1 Jul 2004 07:27:37 -0300 (ADT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id i619aSFm008894; Thu, 1 Jul 2004 12:36:28 +0300 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id i619aR3q008890; Thu, 1 Jul 2004 12:36:27 +0300 Date: Thu, 1 Jul 2004 12:36:27 +0300 (EEST) From: Achilleus Mantzios To: Phil Endecott Cc: Kenneth Gonsalves , Subject: Re: finding if a foreign key is in use In-Reply-To: <40E3E003.4050405@chezphil.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/5 X-Sequence-Number: 18257 O kyrios Phil Endecott egrapse stis Jul 1, 2004 : > >>>in my app i have a table where the id serves as a foreign key for > >>>one or more other tables. if i want to delete a row in the table, > >>>i currently search the other tables where this table is referenced > >>>to see if the row is in use - and then, if not in use, permit > >>>deletion. > >>>Now if i want the delete button in my app to be disabled whenever > >>>a row that is in use is selected, searching the database every time > >>>would dramatically slow down the app. There's an alternative approach to take. Educate your users to be familiar with PostgreSQL error messages (E.g. ERROR: update or delete on "vessels" violates foreign key constraint "$1" on "certificates" ) > >> > >>Basically you do have to do this search. But it won't be too slow if you > >>create an index on the foreign key. > > > > pity. thought postgres would have some function like 'in_use' to tell when a > > row that is used as a foreign key is in actual use and hence cannot be > > deleted. surely, in a database of millions of records, it wouldnt have search > > them all to find if the row is in use? > > It doesn't "search them all" if you have an index. If your database has > a million records it needs to look at only 20 index entries, as 2^20 is > about a million. (At least that's what I, naively, think it should do - > anyone who knows more want to correct me?) > > --Phil. > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- -Achilleus