RE: Re: Are circular REFERENCES possible ?

<p><font size="2">-----BEGIN PGP SIGNED MESSAGE-----</font><br /><font size="2">Hash: SHA1</font><p><font size="2">The
easiestway out is probably to create a relationship entity</font><br /><font size="2">called 'default' between customer
andshop for the default</font><br /><font size="2">relationship.  This way you only have to have one direct</font><br
/><fontsize="2">relationship, because the other is expressed through the 'default'</font><br /><font
size="2">table.</font><p><fontsize="2">Just a thought...</font><br /><p><font size="2">MikeA</font><br /><br /><p><font
size="2">>>-----Original Message-----</font><br /><font size="2">>> From: William Courtier [<a
href="mailto:wcourtier@travelprice.com">mailto:wcourtier@travelprice.com</a>]</font><br/><font size="2">>> Sent:
07August 2001 11:10</font><br /><font size="2">>> To: pgsql-sql@postgresql.org</font><br /><font
size="2">>>Subject: [SQL] Re: Are circular REFERENCES possible ?</font><br /><font size="2">>> </font><br
/><fontsize="2">>> </font><br /><font size="2">>> I denis,</font><br /><font size="2">>> </font><br
/><fontsize="2">>> I don't know if you can use a circular REFERENCES, but why </font><br /><font
size="2">>>you try made a</font><br /><font size="2">>> references before the table is created (customers).
You</font><br /><font size="2">>> should create the</font><br /><font size="2">>> references after and use
theforeign key if circular </font><br /><font size="2">>> references does not</font><br /><font size="2">>>
work.</font><br/><font size="2">>> </font><br /><font size="2">>> William</font><br /><font
size="2">>>"Denis Bucher" <dbucher@niftycom.com> a écrit dans le message</font><br /><font
size="2">>>news: 5.1.0.14.0.20010807114609.00a18490@mail.niftycom.com...</font><br /><font size="2">>>
></font><br/><font size="2">>> > Hello !</font><br /><font size="2">>> ></font><br /><font
size="2">>>> I have a case where I wanted to do circular REFERENCES, is this</font><br /><font
size="2">>>> impossible ?</font><br /><font size="2">>> ></font><br /><font size="2">>> >
Justan example where it would be useful :</font><br /><font size="2">>> ></font><br /><font size="2">>>
>We deliver to the *shops* of our *customers*.</font><br /><font size="2">>> > We have therefore two tables
:</font><br/><font size="2">>> >   - customers (enterprise, financial information, and so on...)</font><br
/><fontsize="2">>> >   - shop (with a name, street, phone number, name of manager)</font><br /><font
size="2">>>></font><br /><font size="2">>> > Now, each shop REFERENCES a customer so that we
know</font><br/><font size="2">>> > to which customer belongs a shop.</font><br /><font size="2">>>
></font><br/><font size="2">>> > AND, each customer has a DEFAULT shop for deliveries, i.e. most</font><br
/><fontsize="2">>> > customers only have one shop, or a main shop and many small</font><br /><font
size="2">>>> ones. Therefore a customer should REFERENCES the 'main' or </font><br /><font size="2">>>
'default'shop.</font><br /><font size="2">>> ></font><br /><font size="2">>> > Which leads to
:</font><br/><font size="2">>> ></font><br /><font size="2">>> > CREATE TABLE shops ( id_shop SERIAL
PRIMARYKEY, id_cust integer</font><br /><font size="2">>> REFERENCES</font><br /><font size="2">>> >
customers,.......)</font><br /><font size="2">>> > CREATE TABLE customers ( id_cust SERIAL PRIMARY
KEY,</font><br/><font size="2">>> > id_defaultshop </font><br /><font size="2">>> integer</font><br
/><fontsize="2">>> > REFERENCES shops, .......)</font><br /><font size="2">>> ></font><br /><font
size="2">>>> But this doesn't work ! Postgres complains like "ERROR: </font><br /><font size="2">>> >
Relation'customers' does not exist"</font><br /><font size="2">>> > when creating 'shops'.</font><br /><font
size="2">>>></font><br /><font size="2">>> > Someone told me I should create a third table, ok, but
in</font><br /><font size="2">>> this case I</font><br /><font size="2">>> loose</font><br /><font
size="2">>>> the total</font><br /><font size="2">>> > control about my logic... Do you have a
suggestion?</font><br /><font size="2">>> ></font><br /><font size="2">>> > Thanks a lot in advance
!</font><br/><font size="2">>> ></font><br /><font size="2">>> > Denis</font><br /><font
size="2">>>></font><br /><font size="2">>> ></font><br /><font size="2">>> >
---------------------------(endof </font><br /><font size="2">>> broadcast)---------------------------</font><br
/><fontsize="2">>> > TIP 1: subscribe and unsubscribe commands go to </font><br /><font size="2">>>
majordomo@postgresql.org</font><br/><font size="2">>> </font><br /><font size="2">>> </font><br /><font
size="2">>></font><br /><font size="2">>> ---------------------------(end of </font><br /><font
size="2">>>broadcast)---------------------------</font><br /><font size="2">>> TIP 2: you can get off all
listsat once with the unregister</font><br /><font size="2">>> command </font><br /><font size="2">>>    
(send"unregister YourEmailAddressHere" to </font><br /><font size="2">>> majordomo@postgresql.org)</font><br
/><fontsize="2">>> </font><p><font size="2">-----BEGIN PGP SIGNATURE-----</font><br /><font size="2">Version:
PGPfreeware6.5.3 for non-commercial use <<a href="http://www.pgp.com"
target="_blank">http://www.pgp.com</a>></font><p><font
size="2">iQA/AwUBO3kkqnympNV/C086EQKcWgCfd1Z2Hbi/g7Rj633Myj67HxkjgvkAn1n+</font><br/><font
size="2">hXvHqca0bqE73XY4tmjDq/7v</font><br/><font size="2">=2nf2</font><br /><font size="2">-----END PGP
SIGNATURE-----</font><code><fontsize="3"><br /><br />
_________________________________________________________________________<br/> This e-mail and any attachments are
confidentialand may also be privileged and/or copyright <br /> material of Intec Telecom Systems PLC (or its affiliated
companies).If you are not an <br /> intended or authorised recipient of this e-mail or have received it in error,
pleasedelete <br /> it immediately and notify the sender by e-mail. In such a case, reading, reproducing, <br />
printingor further dissemination of this e-mail is strictly prohibited and may be unlawful. <br /> Intec Telecom
SystemsPLC. does not represent or warrant that an attachment hereto is free <br /> from computer viruses or other
defects.The opinions expressed in this e-mail and any <br /> attachments may be those of the author and are not
necessarilythose of Intec Telecom <br /> Systems PLC. <br /><br /> This footnote also confirms that this email message
hasbeen swept by<br /> MIMEsweeper for the presence of computer viruses. <br />
__________________________________________________________________________<br/></font></code>