Skip site navigation (1) Skip section navigation (2)

Re: REFERENCES to foreign tables

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: D(dot)C(dot) <coughlandesmond(at)yahoo(dot)fr>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: REFERENCES to foreign tables
Date: 2005-05-21 12:36:02
Message-ID: 4709C8AF-24DE-496D-B952-41C66295737D@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2005-05-21 14:44:07
Subject: Re: postgresql-dump .. catch 22 ?
Previous:From: John DeSoiDate: 2005-05-21 12:27:33
Subject: Re: REFERENCES to foreign tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group