Обсуждение: Are circular REFERENCES possible ?

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

Aug 7, 11:54 +0200, Denis Bucher wrote:

> 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 ?

We have next conditions:
Each customer has many shops;
Each shop belong to one and only one customer;
So, you have a classic one -> many relation.
Next. Shop may have additional attribute - 'default'.
You can add column 'default bool' to the table 'shops' but then you should
implement something like trigger to check that only one shop of each
customer will have 'default' = 't';
Of cause, you can create third table like
create table default_shop(id_shop int not null primary key references
shops);
but it doesn't make a sense, you again must implement something to keep
your business logic like trigger which will check that only one shop from
belong to a customer there is in the table.
So, I think, best solution is add column 'default bool' in the table
'shop' and create a trigger before insert on shop which will check if
inserted row has 'default' ='t' are there a row which already has 'default'
= 't'. May be you will want a trigger for update. And may be for delete -
if will deleted 'default' shop - what we should to do?
Better will be create a function for delete shop which will take id_shop
which will be deleted and id_shop which will be default as arguments and
this function will delete one shop and assign an other as default.



my best regards,
----------------
Grigoriy G. Vovk

>
>
>Which leads to :
>
>CREATE TABLE shops ( id_shop SERIAL PRIMARY KEY, id_cust integer 
>REFERENCES customers, .......)

You can't reference to a table who doesn't exists still.


>CREATE TABLE customers ( id_cust SERIAL PRIMARY KEY, id_defaultshop 
>integer REFERENCES shops, .......)

Perhaps you can do it something like that if:
1.- Create the two tables.
2.- Use alter table to add the constraint "references".

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.

> 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.

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 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.

So, an alternate solution to your database structure:

1. Each Customer has one to many Shops (Shops.CustomerID REFERENCES
Customers(ID)).
2. Each Shop has a Boolean characteristic Default.
3. Of a Customer's shops, only one can have Default=TRUE at any one
time.

You use triggers or functions to enforce rule 3.  This system works
quite well for this purpose ... I was able to put it to use for a much
more complex CRM system with main and secondary HR and billing
addresses.

Your third alternative is to create a JOIN table called Default Shops.
However, this does not really provide you any additional referential
integrity -- it jsut may suit you if you find triggers intimidating.


-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

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     

On Tue, 7 Aug 2001, 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.
> 
> > 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.
> 
> 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.

This is actually not quite true.  You need to make the references in
a circular relationship deferrable andprobably initially deferred and then  
add pairs if necessary within one transaction (note: there are some bugs
in deferred constraints if you do somewhat wierd things)

The other tricks are things like for deletes, you may want to use
on delete set null for the the default shop on deliveries (ie, if the
shop they use is deleted, they don't have a default shop until
someone gives them one).

However, I agree that generally circular constraints are painful and its
often better to think of another way to hold the relationship.

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
wrapall the   changes that put the database into a  temporary  inconsistent   state  into  a  transaction. What is a
goodidea and strongly   advised anyway.
 
   DEFERRED means, that the  consistency  WRT  the  foreign  key   constratins  will  be  checked  at COMMIT time
insteadof 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

Jan,
>     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

Hmmm... sounds interesting.  Can this be done through functions?  I.E.,
if I put the INSERT/INSERT/UPDATE operation inside a function, does it
automatically wait until the function completes before checking
constraints?

>     Josh, maybe you should buy a newer SQL-bo... :-)
> 
>     Got ya (LOL)!

Zap!  Ouch.  ;-)

> 
>     The point is that we based our implementation of foreign keys
>     on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK.
> 

Know a good SQL3 book?  I bought O'Reilly's SQL In A Nutshell for that,
but the book has numerous omissions and a few mistakes.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     The point is that we based our implementation of foreign keys
> >     on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK.
>
> I still have a concern about this --- sure, you can set up the circular
> references using ALTER TABLE, but will pg_dump dump them correctly?
   AFAIK yes.
   I'm  not  sure if it still uses the CONSTRAINT TRIGGER syntax   or does it now with ALTER TABLE. But for sure it
creates all   tables  first,  then  loads  the  data,  then  activates  the   constraints (wouldn't work very well
otherwise).


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

Jan Wieck <JanWieck@Yahoo.com> writes:
>     The point is that we based our implementation of foreign keys
>     on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK.

I still have a concern about this --- sure, you can set up the circular
references using ALTER TABLE, but will pg_dump dump them correctly?
        regards, tom lane

Josh Berkus wrote:
> Jan,
>
> >     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
>
> Hmmm... sounds interesting.  Can this be done through functions?  I.E.,
> if I put the INSERT/INSERT/UPDATE operation inside a function, does it
> automatically wait until the function completes before checking
> constraints?
   Acutally  you  have  fine  control  over  it  if you name the   constraints explicitly. You  can  define  a
constraint just   beeing  DEFERRABLE but INITIALLY IMMEDIATE. Such a constraint   will by default be checked
immediatelyat the time a PK/FK is   touched.  Inside  of  your  function  (as  well  as  inside a   transaction from
theapp-level) you can
 
       SET CONSTRAINTS namelist DEFERRED;
       do all your inserts/updates;
       SET CONSTRAINTS namelist IMMEDIATE;
   Setting them to DEFERRED means, that the checks  for  primary   key  existence  on  make  of references or the check
fornon-   existence of references on destruction  of  primary  key  are   delayed,  at max until COMMIT. Setting them
backto IMMEDIATE   runs the checks "for these constraint"  immediately,  without   waiting  for the COMMIT, and
arrangesfor all further actions   to get checked immediately.
 
   Whatever  you  do  and  in  whatever  state  you  leave   the   constraints, everything not yet checked will be at
COMMIT.
   Well,  the  SET  CONSTRAINTS has to be put into an EXECUTE in   PL/pgSQL, but I think that's not too big of a
problem.

> >     Josh, maybe you should buy a newer SQL-bo... :-)
> >
> >     Got ya (LOL)!
>
> Zap!  Ouch.  ;-)
   Couldn't resist ;-P

> >     The point is that we based our implementation of foreign keys
> >     on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK.
> >
>
> Know a good SQL3 book?  I bought O'Reilly's SQL In A Nutshell for that,
> but the book has numerous omissions and a few mistakes.
   Unfortunately no - others?


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

Hi all,

On Tuesday 07 August 2001  7:35 pm, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     The point is that we based our implementation of foreign keys
> >     on the SQL3 specs. DEFERRED is not in SQL-92 AFAIK.
>
> I still have a concern about this --- sure, you can set up the circular
> references using ALTER TABLE, but will pg_dump dump them correctly?

Based on a small example I've done, I'd say yes. This is because pg_dump 
doesn't specify the contraints when it creates the tables, it does it at the 
end by using 'CREATE CONSTRAINT' commands.

I have a relationship Members -> teams -> regions -> members and it dumped 
and restored fine.

Gary

>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" 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     

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" <dbucher@niftycom.com> 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

<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> 

Hi Mike,

A few people have suggested this, but the thing I don't like (and I think at 
some point William has also stated this) is that doing it this way, you lose 
the logic (and the purity) of the data.  If you allow the cyclic reference, 
then the data behaves -and looks - exactly as it should do. The customer 
refers to the shop, and the shop refers to the customer.

If I remember correctly, one of the cardinal rules of normalising data is 
that all related data (e.g. customer) should be together (one table) - hense, 
the default_shop belongs to the customer table
.  
Relationship table should only be used for n-to-n links.

Gary

On Tuesday 14 August 2001  2:16 pm, Michael Ansley (UK) wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> The easiest way out is probably to create a relationship entity
> called 'default' between customer and shop for the default
> relationship.  This way you only have to have one direct
> relationship, because the other is expressed through the 'default'
> table.
>
> Just a thought...
>
>
> MikeA
>
> >> -----Original Message-----
> >> From: William Courtier [mailto:wcourtier@travelprice.com]
> >> Sent: 07 August 2001 11:10
> >> To: pgsql-sql@postgresql.org
> >> Subject: [SQL] Re: Are circular REFERENCES possible ?
> >>
> >>
> >> 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" <dbucher@niftycom.com> 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
> >>
> >>
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 2: you can get off all lists at once with the unregister
> >> command
> >>     (send "unregister YourEmailAddressHere" to
> >> majordomo@postgresql.org)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>
>
> iQA/AwUBO3kkqnympNV/C086EQKcWgCfd1Z2Hbi/g7Rj633Myj67HxkjgvkAn1n+
> hXvHqca0bqE73XY4tmjDq/7v
> =2nf2
> -----END PGP SIGNATURE-----

----------------------------------------
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
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