pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Are circular REFERENCES possible ?
15+ messages / 10 participants
[nested] [flat]

* Are circular REFERENCES possible ?
@ 2001-08-07 09:54  Denis Bucher <dbucher@niftycom.com>
  0 siblings, 5 replies; 15+ messages in thread

From: Denis Bucher @ 2001-08-07 09:54 UTC (permalink / raw)
  To: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 10:09  William Courtier <wcourtier@travelprice.com>
  parent: Denis Bucher <dbucher@niftycom.com>
  4 siblings, 0 replies; 15+ messages in thread

From: William Courtier @ 2001-08-07 10:09 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 10:16  Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
  parent: Denis Bucher <dbucher@niftycom.com>
  4 siblings, 0 replies; 15+ messages in thread

From: Grigoriy G. Vovk @ 2001-08-07 10:16 UTC (permalink / raw)
  To: Denis Bucher <dbucher@niftycom.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 10:26  David Lizano <david.lizano@izanet.com>
  parent: Denis Bucher <dbucher@niftycom.com>
  4 siblings, 0 replies; 15+ messages in thread

From: David Lizano @ 2001-08-07 10:26 UTC (permalink / raw)
  To: pgsql-sql


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





^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 15:01  Josh Berkus <josh@agliodbs.com>
  parent: Denis Bucher <dbucher@niftycom.com>
  4 siblings, 2 replies; 15+ messages in thread

From: Josh Berkus @ 2001-08-07 15:01 UTC (permalink / raw)
  To: Denis Bucher <dbucher@niftycom.com>; +Cc: pgsql-sql

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







^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 15:38  Gary Stainburn <gary.stainburn@ringways.co.uk>
  parent: Denis Bucher <dbucher@niftycom.com>
  4 siblings, 0 replies; 15+ messages in thread

From: Gary Stainburn @ 2001-08-07 15:38 UTC (permalink / raw)
  To: Denis Bucher <dbucher@niftycom.com>; pgsql-sql

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     



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 16:24  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 15+ messages in thread

From: Stephan Szabo @ 2001-08-07 16:24 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Denis Bucher <dbucher@niftycom.com>; pgsql-sql


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.




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 16:54  Jan Wieck <JanWieck@Yahoo.com>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 2 replies; 15+ messages in thread

From: Jan Wieck @ 2001-08-07 16:54 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Denis Bucher <dbucher@niftycom.com>; pgsql-sql

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

        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




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 17:52  Josh Berkus <josh@agliodbs.com>
  parent: Jan Wieck <JanWieck@Yahoo.com>
  1 sibling, 1 reply; 15+ messages in thread

From: Josh Berkus @ 2001-08-07 17:52 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; Josh Berkus <josh@agliodbs.com>; +Cc: Denis Bucher <dbucher@niftycom.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 18:35  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jan Wieck <JanWieck@Yahoo.com>
  1 sibling, 1 reply; 15+ messages in thread

From: Tom Lane @ 2001-08-07 18:35 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Denis Bucher <dbucher@niftycom.com>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 18:51  Jan Wieck <JanWieck@Yahoo.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Jan Wieck @ 2001-08-07 18:51 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Denis Bucher <dbucher@niftycom.com>; pgsql-sql

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 immediately at the time a PK/FK is
    touched.  Inside  of  your  function  (as  well  as  inside a
    transaction from the app-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 for non-
    existence of references on destruction  of  primary  key  are
    delayed,  at max until COMMIT. Setting them back to IMMEDIATE
    runs the checks "for these constraint"  immediately,  without
    waiting  for the COMMIT, and arranges for 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




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-07 19:16  Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Jan Wieck @ 2001-08-07 19:16 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Are circular REFERENCES possible ?
@ 2001-08-08 08:43  Gary Stainburn <gary.stainburn@ringways.co.uk>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 15+ messages in thread

From: Gary Stainburn @ 2001-08-08 08:43 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Jan Wieck <JanWieck@yahoo.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Denis Bucher <dbucher@niftycom.com>; pgsql-sql

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     



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* RE: Re: Are circular REFERENCES possible ?
@ 2001-08-14 13:16  Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Michael Ansley (UK) @ 2001-08-14 13:16 UTC (permalink / raw)
  To: 'William Courtier' <wcourtier@travelprice.com>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Re: Are circular REFERENCES possible ?
@ 2001-08-14 15:12  Gary Stainburn <gary.stainburn@ringways.co.uk>
  parent: Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Gary Stainburn @ 2001-08-14 15:12 UTC (permalink / raw)
  To: Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>; 'William Courtier' <wcourtier@travelprice.com>; +Cc: pgsql-sql

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     



^ permalink  raw  reply  [nested|flat] 15+ messages in thread


end of thread, other threads:[~2001-08-14 15:12 UTC | newest]

Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-07 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
2001-08-07 10:09 ` William Courtier <wcourtier@travelprice.com>
2001-08-07 10:16 ` Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
2001-08-07 10:26 ` David Lizano <david.lizano@izanet.com>
2001-08-07 15:01 ` Josh Berkus <josh@agliodbs.com>
2001-08-07 16:24   ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-07 16:54   ` Jan Wieck <JanWieck@Yahoo.com>
2001-08-07 17:52     ` Josh Berkus <josh@agliodbs.com>
2001-08-07 18:51       ` Jan Wieck <JanWieck@Yahoo.com>
2001-08-07 18:35     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-08 08:43       ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-08-07 15:38 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-08-07 19:16 Re: Are circular REFERENCES possible ? Jan Wieck <JanWieck@Yahoo.com>
2001-08-14 13:16 RE: Re: Are circular REFERENCES possible ? Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
2001-08-14 15:12 ` Gary Stainburn <gary.stainburn@ringways.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox