Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id MAA22397 for ; Thu, 27 Jan 2000 12:37:53 -0500 (EST) (envelope-from wieck@debis.com) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m12Dsgh-0003kGC; Thu, 27 Jan 100 18:26 MET Message-Id: From: wieck@debis.com (Jan Wieck) Subject: Re: [SQL] Rules, triggers, ??? - What is the best way to enforce data-valid ation tests?? In-Reply-To: <2B9713A99045D211BB4E0008C7566869DC9F8C@scorpexc1.houstoncell.com> from Greg Youngblood at "Jan 12, 2000 11:52:51 am" To: Greg Youngblood Date: Thu, 27 Jan 2000 18:26:31 +0100 (CET) CC: "'PostgreSQL SQL List'" Reply-To: Jan Wieck X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > I am in the process of creating a large relational database. One of the key > things I need to include in this database is a system to maintain data > integrity across multiple tables. > > here's an example: > > [...] > > In the past I've tried triggers and rules and never did get them to work to > my satisfaction. Either they would work for some tasks, but I couldn't adapt > them to work in more complicated tables, or there were syntax problems and I > never got them to work at all. If anyone can help, and provide some > examples, i would greatly appreciate it. > > I currently have postgres 6.5.0 on the production server, and 6.5.1 or 6.5.2 > on my development boxes. Version 7.0 will have support for FOREIGN KEY syntax, that's what you're looking for. I'm not sure if ALTER TABLE ... ADD CONSTRAINT will be implemented, but at least you can define all the dependencies during CREATE TABLE. The support not only includes the checks about key existance as in your example. You can also specify how the DB should behave if a referenced key from the primary table is deleted or updated (for example let references automatically follow, do cascaded deletes or simply reject the modification as long as the PK is referenced). Get a snapshot of the current development sources from our CVS server using anon-CVS. Most of the functionality is already there. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #