Re: REFERENCES to foreign tables
От | D.C. |
---|---|
Тема | Re: REFERENCES to foreign tables |
Дата | |
Msg-id | ef58b23f89171ed15d6cc8e5ff047281@yahoo.fr обсуждение исходный текст |
Ответ на | Re: REFERENCES to foreign tables (Michael Glaesemann <grzm@myrealbox.com>) |
Список | 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 ?
В списке pgsql-novice по дате отправления: