Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59]) by postgresql.org (8.11.3/8.11.4) with SMTP id f77GlP606397 for ; Tue, 7 Aug 2001 12:47:26 -0400 (EDT) (envelope-from janwieck@yahoo.com) Received: from jupiter.us.greatbridge.com (65.196.69.55) by smtp.mail.vip.sc5.yahoo.com with SMTP; 7 Aug 2001 16:47:24 -0000 X-Apparently-From: Received: (from wieck@localhost) by jupiter.us.greatbridge.com (8.11.2/8.11.2) id f77GssQ01998; Tue, 7 Aug 2001 12:54:54 -0400 From: Jan Wieck Message-Id: <200108071654.f77GssQ01998@jupiter.us.greatbridge.com> Subject: Re: Are circular REFERENCES possible ? In-Reply-To: from Josh Berkus at "Aug 7, 2001 08:01:45 am" To: Josh Berkus Date: Tue, 7 Aug 2001 12:54:54 -0400 (EDT) CC: Denis Bucher , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200108/104 X-Sequence-Number: 4325 Josh Berkus wrote: > Denis, > > > I have a case where I wanted to do circular REFERENCES, is this > > impossible ? > > It can be done. It's just a bad idea. I don't see why it is a bad idea to apply the full business model to the database schema. > > Now, each shop REFERENCES a customer so that we know > > to which customer belongs a shop. > > > > AND, each customer has a DEFAULT shop for deliveries, i.e. most > > customers only have one shop, or a main shop and many small ones. > > Therefore a customer should REFERENCES the 'main' or 'default' shop. > > You can do this by applying the constraints *after* table creation. > However, you will forever fight the following problems: > > 1. You will not be able to add any records to Customers without dropping > and re-creating the REFERENCES each time. > 2. You will never be able to delete a record from either table due to > the circular reference check. > 3. Some UPDATES will also fail for the same reason. All of this is wrong. If the constraints are defined to be INITIALLY DEFERRED, all you have to do is to wrap all the changes that put the database into a temporary inconsistent state into a transaction. What is a good idea and strongly advised anyway. DEFERRED means, that the consistency WRT the foreign key constratins will be checked at COMMIT time instead of the actual statement. So if you BEGIN TRANSACTION; INSERT INTO customer ... INSERT INTO shop ... COMMIT TRANSACTION; It'll get you out of the circular problem without dropping and re-creating the constraints. The same applies to updates and deletes generally. Well, if you want to you can specify ON UPDATE CASCADE and ON DELETE CASCADE, so if you delete a shop, the customers referencing it will get deleted automatically too, which might cause other shops referencing them ... > All of this makes circular references a bad idea; references are meant > to be heirarchical. Heck, I got into a real mess when I accidentally > set up a circular reference among 5 tables ... took me forever to figure > out why INSERTS kept failing. Josh, maybe you should buy a newer SQL-bo... :-) Got ya (LOL)! The point is that we based our implementation of foreign keys on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com