[NOVICE] REFERENCES to foreign tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[NOVICE] REFERENCES to foreign tables

Desmond Coughlan
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.



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] REFERENCES to foreign tables

John DeSoi

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] REFERENCES to foreign tables

Michael Glaesemann
In reply to this post by Desmond Coughlan

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] REFERENCES to foreign tables

Desmond Coughlan
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 ?


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster