Re: Are circular REFERENCES possible ?

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