Received: from relay.mail.insnet.cw.net (relay.mail.insnet.cw.net [213.38.238.97]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f77FfF683991 for ; Tue, 7 Aug 2001 11:41:15 -0400 (EDT) (envelope-from gary.stainburn@ringways.co.uk) Received: from stan.ringways.co.uk ([213.38.87.130]) by relay.mail.insnet.cw.net (8.11.2/8.11.2) with ESMTP id f77Ff8k19517; Tue, 7 Aug 2001 16:41:09 +0100 (BST) Received: from gary.ringways.co.uk ([10.1.1.2]) by stan.ringways.co.uk with smtp (Exim 3.13 #1) id 15U8l3-0002eI-00; Tue, 07 Aug 2001 16:27:01 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: Denis Bucher , PGSQL-SQL Subject: Re: Are circular REFERENCES possible ? Date: Tue, 7 Aug 2001 16:38:28 +0100 X-Mailer: KMail [version 1.2] References: <5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com> In-Reply-To: <5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com> MIME-Version: 1.0 Message-Id: <01080716382807.22099@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200108/100 X-Sequence-Number: 4321 Hi Denis, I've just had a similar experience with a 3-way circle. I have members, who belong in regions. Each region had a Regional Liasson Officer who was a member. I got round it by creating the three tables, but missing out one of the references - i.e. the one that links table 1 to table 3 which doesn't exist yet. I then used pg_dump to see how that would re-create the tables. It didn't create any references/foreign keys etc. when it created the tables, but right at the end, aftter the 'copy's and index creations it did a load of CREATE CONSTRACT TRIGGER entries. I edited these to generate the ones that were missing. This was a bit messy, but it meant that I could keep the logic of my data. As stated in some of the other posts, you will have problems updating your data, with inserts. One thing to remember here is that references aren't checked if the reference value is NULL. So, you could add a customer with the default shop as NULL, then add a shop, and then update the customer. I haven't checked this, but I seam to remember reading that if you do it all inside a transaction, the references aren't checked until the transaction is comitted, so you could do something like: begin insert customer insert shop comit Gary On Tuesday 07 August 2001 10:54 am, Denis Bucher wrote: > Hello ! > > I have a case where I wanted to do circular REFERENCES, is this > impossible ? > > Just an example where it would be useful : > > We deliver to the *shops* of our *customers*. > We have therefore two tables : > - customers (enterprise, financial information, and so on...) > - shop (with a name, street, phone number, name of manager) > > 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. > > Which leads to : > > CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer REFERENCES > customers, .......) > CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop integer > REFERENCES shops, .......) > > But this doesn't work ! Postgres complains like "ERROR: Relation > 'customers' does not exist" > when creating 'shops'. > > Someone told me I should create a third table, ok, but in this case I loose > the total > control about my logic... Do you have a suggestion ? > > Thanks a lot in advance ! > > Denis > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000