Re: REFERENCES to foreign tables

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 ?