Обсуждение: REFERENCES to foreign tables
Hi (not sure if the Subject: field is correct, but here goes ..), 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 ? Thanks in advance. D.
On May 21, 2005, at 8:08 AM, D.C. wrote: > 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 ? You need to decide what you want to happen when deleting a client row. You can specify this as part of the foreign key declaration with the 'ON DELETE' specification. You can have the database delete the client and sales record or it can be setup to generate an error if this is attempted (the default). So using the defaults, you'll be able to delete a client with no sales but you'll get an error otherwise. See the CREATE TABLE documentation for the syntax and all of the options. http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
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 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? Michael Glaesemann grzm myrealbox com
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 ?
