Skip site navigation (1) Skip section navigation (2)

Re: REFERENCES to foreign tables

From: D(dot)C(dot) <coughlandesmond(at)yahoo(dot)fr>
To: Michael Glaesemann <grzm(at)myrealbox(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: REFERENCES to foreign tables
Date: 2005-05-21 18:20:28
Message-ID: ef58b23f89171ed15d6cc8e5ff047281@yahoo.fr (view raw or flat)
Thread:
Lists: pgsql-novice
X-No-Archive: true

Le 21 mai 05, à 14:36, Michael Glaesemann a écrit :

> On May 21, 2005, at 9:08 PM, D.C. wrote:
>> I'm trying to build some more complexity into the db that I mentioned 
>> recently.  If in the 'clients' table, I have
>>
>> client_id int NOT NULL PRIMARY KEY CHECK (client_id > 0)
>> name varchar(50) NOT NULL
>> [...]
>>
>> ... and in the 'sales' table, I have ...
>>
>> sale_id int NOT NULL PRIMARY KEY CHECK (sale_id > 0)
>> sold_to INT NOT NULL REFERENCES clients (client_id)
>> [...]
>>
>> So when a sale is entered, postgreSQL keeps a record of who it was 
>> sold to.  Now what happens if I want to delete the client who bought 
>> the item in question ?  I'm going to have a 'sales' table that 
>> references a buyer who no longer exists (in db terms, naturally).  I 
>> wondered aloud (talking to the wall, sort of) if I could reference 
>> clients.client_id to sales.sale_id, but wouldn't that stop my adding 
>> a client who hasn't yet made a purchase ?
>
> A couple of thoughts. I assume you want to keep the sales around even 
> after you've deleted the client. If this is the case, then I think you 
> have two options. One is to add a "deleted" boolean column to your 
> clients table (probably with a default value of false to make inserts 
> a bit more convenient). You won't actually delete any of the clients, 
> but rather update deleted to true when you no longer want them around.
>
> The other option is to remove the sold_to column from the sales table, 
> creating a sales_clients join table instead which references both the 
> clients and sales tables. Then, when you delete a client, the 
> corresponding rows from the sales_clients table will be deleted as 
> well (depending on your cascade settings). Your sales will still be in 
> the sales table, but there will no longer be any client associated 
> with the sale. Depending on your model, you probably will want to put 
> a unique(sales_id) constraint on the sales_clients table to restrict 
> the number of clients per sale to 1, which I assume is consistent with 
> your current sales table.

I like this idea better: thanks.  I'm going to have to go and learn 
about CASCADE.

> I haven't built a system like this, but this is how I'd go about it. 
> I'd be interested in hearing others thoughts as well.
>
> I'm not familiar with your earlier post concerning this db, so perhaps 
> this isn't applicable, but a few points:
> 1. "name" is a reserved SQL key word and may cause unexpected results. 
> I'd change the clients.name attribute to something like 
> clients.client_name.
> 2. I'd also put a unique(client_name) constraint on the clients table 
> to guarantee I don't duplicate client names. That can get a little 
> confusing :)
> 3. Any reason not to use serial types for your client_id and sale_id 
> attributes? They're very convenient, and are guaranteed unique. Ids 
> such as these are often kept hidden from the user. If you have another 
> id you need to enter, for example one generated by another system, you 
> might want to keep that as an added column. This allows you a bit more 
> flexibility in case of future changes.
>
> Just my ¥2. Take it or leave it :)
>
> Does this help?

It does indeed: good points all.  The 'name' that I put on the table 
was to illustrate the point for the group.  It in fact looks like this 
(after I incorporated your ideas about SERIAL types) ..

<http://www.chez.com/desmondcoughlan/sql/table.jpg>

Thanks for the feedback !

D.

PS I've just seen that my e-mails to this mailing list appear in my 
newsreader, with my e-mail address intact.  This is Not Good.  How can 
I have them removed before I see my e-mail address become unusable 
through spam ?


In response to

pgsql-novice by date

Next:From: Adam BogackiDate: 2005-05-22 04:20:46
Subject: Fixing postmaster (7.2->7.4.8)
Previous:From: Tom LaneDate: 2005-05-21 14:44:07
Subject: Re: postgresql-dump .. catch 22 ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group