Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.4) with SMTP id f7ECxEP01759 for ; Tue, 14 Aug 2001 08:59:14 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f77AWN691168 for ; Tue, 7 Aug 2001 06:32:23 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f77A9xh04164 for pgsql-sql@postgresql.org; Tue, 7 Aug 2001 06:09:59 -0400 (EDT) (envelope-from news) From: "William Courtier" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Are circular REFERENCES possible ? Date: Tue, 7 Aug 2001 12:09:59 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 57 Message-ID: <9koepm$420$1@news.tht.net> References: <5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-sql@postgresql.org X-Archive-Number: 200108/179 X-Sequence-Number: 4401 I denis, I don't know if you can use a circular REFERENCES, but why you try made a references before the table is created (customers). You should create the references after and use the foreign key if circular references does not work. William "Denis Bucher" a écrit dans le message news: 5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com... > > 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