Thread: REFERENCES to foreign tables

REFERENCES to foreign tables

From
D.C.
Date:
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.



Re: REFERENCES to foreign tables

From
John DeSoi
Date:
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


Re: REFERENCES to foreign tables

From
Michael Glaesemann
Date:
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


Re: REFERENCES to foreign tables

From
D.C.
Date:
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 ?