pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAre circular REFERENCES possible ?
15+ messages / 10 participants
[nested] [flat]
* Are circular REFERENCES possible ?
@ 2001-08-07 09:54 Denis Bucher <dbucher@niftycom.com>
2001-08-07 10:09 ` Re: Are circular REFERENCES possible ? William Courtier <wcourtier@travelprice.com>
2001-08-07 10:16 ` Re: Are circular REFERENCES possible ? Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
2001-08-07 10:26 ` Re: Are circular REFERENCES possible ? David Lizano <david.lizano@izanet.com>
2001-08-07 15:01 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
2001-08-07 15:38 ` Re: Are circular REFERENCES possible ? Gary Stainburn <gary.stainburn@ringways.co.uk>
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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
@ 2001-08-07 10:09 ` William Courtier <wcourtier@travelprice.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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
@ 2001-08-07 10:16 ` Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
@ 2001-08-07 10:26 ` David Lizano <david.lizano@izanet.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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
@ 2001-08-07 15:01 ` Josh Berkus <josh@agliodbs.com>
2001-08-07 16:24 ` Re: Are circular REFERENCES possible ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-07 16:54 ` Re: Are circular REFERENCES possible ? Jan Wieck <JanWieck@Yahoo.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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
2001-08-07 15:01 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
@ 2001-08-07 16:24 ` Stephan Szabo <sszabo@megazone23.bigpanda.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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
2001-08-07 15:01 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
@ 2001-08-07 16:54 ` Jan Wieck <JanWieck@Yahoo.com>
2001-08-07 17:52 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
2001-08-07 18:35 ` Re: Are circular REFERENCES possible ? Tom Lane <tgl@sss.pgh.pa.us>
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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
2001-08-07 15:01 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
2001-08-07 16:54 ` Re: Are circular REFERENCES possible ? Jan Wieck <JanWieck@Yahoo.com>
@ 2001-08-07 17:52 ` Josh Berkus <josh@agliodbs.com>
2001-08-07 18:51 ` Re: Are circular REFERENCES possible ? 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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
2001-08-07 15:01 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
2001-08-07 16:54 ` Re: Are circular REFERENCES possible ? Jan Wieck <JanWieck@Yahoo.com>
2001-08-07 17:52 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
@ 2001-08-07 18:51 ` Jan Wieck <JanWieck@Yahoo.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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
2001-08-07 15:01 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
2001-08-07 16:54 ` Re: Are circular REFERENCES possible ? Jan Wieck <JanWieck@Yahoo.com>
@ 2001-08-07 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-08 08:43 ` Re: Are circular REFERENCES possible ? Gary Stainburn <gary.stainburn@ringways.co.uk>
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 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
2001-08-07 15:01 ` Re: Are circular REFERENCES possible ? Josh Berkus <josh@agliodbs.com>
2001-08-07 16:54 ` Re: Are circular REFERENCES possible ? Jan Wieck <JanWieck@Yahoo.com>
2001-08-07 18:35 ` Re: Are circular REFERENCES possible ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-08-08 08:43 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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: Are circular REFERENCES possible ?
2001-08-07 09:54 Are circular REFERENCES possible ? Denis Bucher <dbucher@niftycom.com>
@ 2001-08-07 15:38 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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 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: Re: Are circular REFERENCES possible ?
@ 2001-08-14 13:16 Michael Ansley (UK) <Michael.Ansley@intec-telecom-systems.com>
2001-08-14 15:12 ` Re: Re: Are circular REFERENCES possible ? Gary Stainburn <gary.stainburn@ringways.co.uk>
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 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>
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