Обсуждение: 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