Received: from narn.deckpoint.ch (narn.deckpoint.ch [194.38.160.133]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f779qf680879 for ; Tue, 7 Aug 2001 05:52:41 -0400 (EDT) (envelope-from dbucher@niftycom.com) Received: from violet.niftycom.com (hidden-user@[213.162.13.75]) by narn.deckpoint.ch (8.11.4/8.11.4) with ESMTP id f779qdo06275 for ; Tue, 7 Aug 2001 11:52:39 +0200 Message-Id: <5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com> X-Sender: dbucher@mail.niftycom.com X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Tue, 07 Aug 2001 11:54:52 +0200 To: PGSQL-SQL From: Denis Bucher Subject: Are circular REFERENCES possible ? Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200108/91 X-Sequence-Number: 4312 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