X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 72175D1B1C8 for ; Wed, 4 Aug 2004 12:37:32 -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 73142-10 for ; Wed, 4 Aug 2004 15:37:27 +0000 (GMT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id BB661D1B1AA for ; Wed, 4 Aug 2004 12:37:25 -0300 (ADT) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 1BsNpU-000PZH-0V; Wed, 04 Aug 2004 15:37:24 +0000 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.34) id 1BsNpQ-00028t-Ej; Wed, 04 Aug 2004 16:37:20 +0100 Subject: Re: check constraints From: Oliver Elphick Reply-To: olly@lfix.co.uk To: Jodi Kanter Cc: Postgres Admin List In-Reply-To: <4110F79A.2030002@virginia.edu> References: <4110F79A.2030002@virginia.edu> Content-Type: text/plain Organization: LFIX Limited Message-Id: <1091633840.31602.2924.camel@linda> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Wed, 04 Aug 2004 16:37:20 +0100 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-MailScanner-From: olly@lfix.co.uk 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: 200408/19 X-Sequence-Number: 14414 On Wed, 2004-08-04 at 15:50, Jodi Kanter wrote: > I have little experience with postgres' check constraint features and > was hoping to get some guidance. We have a table which houses links to > various user files. The PK of this table is fi_pk. There are several > tables in which a foreign key (fi_fk) to this field exist. We are > implementing a feature which will allow users to remove old files from > their repositories. It would be nice to have a check that goes out to > all tables that have this fi_fk field and checks to see if the fi_pk > for their particular record exists and would then not allow the > deletion if a link occurs somewhere. > I realize that I can do this with specific SQL in the code but was > wondering if it's possible to implement a sort of general database > wide check of this nature. That way if future tables are added with > this FK field then the code would not have to be altered. You do it when you define the foreign key in the other tables: CREATE other_table ( ..., fi_fk ... REFERENCES table (fi_pk) ON UPDATE CASCADE ON DELETE RESTRICT, ... ); This says that if the foreign key field changes its value in table the corresponding value in other_table should be changed too. If an attempt is made to delete the value in table when it is referenced by any rows in other_table, the deletion will not be permnitted. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "And not only so, but we glory in tribulations also; knowing that tribulation worketh patience; And patience, experience; and experience, hope." Romans 5:3,4