Board index » delphi » preventing entries being deleted when primary key is deleted

Tim Chemaly

Delphi Developer
Wed, 04 Feb 2004 05:43:40 GMT

preventing entries being deleted when primary key is deleted

Hi
I'm designing an accounting system using interbase.
I have a table of clients with each client assigned a client id which is my
primary key.
Since this primary key will be used as a foreign key in other tables such as
purchases by clients I'm in a bit of a paradox:-))

My problem is that if I delete a client all subsequent records in the
purchases table will be deleted as well. BUT I don't want this because I
want to be able to determine all purchases and credits, profits etc. over
the year(s) regardless of whether this client still exists. BUT If the
entries aren't deleted from the table how can the foreign key in the record
still point to an entry in the clients table that does not exist?

Can anyone with database experience please help me on this one...I'm really
stuck.

Thanks
Tim

 
Dan Palle

Delphi Developer
Wed, 04 Feb 2004 06:10:51 GMT

Re:preventing entries being deleted when primary key is deleted


You have a couple of options:

- instead of deleting the client, mark it as inactive, leaving all other
records intact.  Your reports can ignore inactive clients.

- Use an ON DELETE SET NULL parameter in your foreign key definition.  This
will make the foreign key fields NULL which is OK; a table that references
another table via a foreign key can have a NULL value as the foreign key
field.

Dan

Quote
"Tim Chemaly" <tchem...@ing.sun.ac.za> wrote in message

news:3b7d900c_2@dnews...
Quote
> Hi
> I'm designing an accounting system using interbase.
> I have a table of clients with each client assigned a client id which is
my
> primary key.
> Since this primary key will be used as a foreign key in other tables such
as
> purchases by clients I'm in a bit of a paradox:-))

> My problem is that if I delete a client all subsequent records in the
> purchases table will be deleted as well. BUT I don't want this because I
> want to be able to determine all purchases and credits, profits etc. over
> the year(s) regardless of whether this client still exists. BUT If the
> entries aren't deleted from the table how can the foreign key in the
record
> still point to an entry in the clients table that does not exist?

> Can anyone with database experience please help me on this one...I'm
really
> stuck.

> Thanks
> Tim

Tim Chemal

Delphi Developer
Wed, 04 Feb 2004 07:18:34 GMT

Re:preventing entries being deleted when primary key is deleted


Hi Dan
I thought that when one uses an ON DELETE SET NULL  ALL the fields in the
row becomes NULL???

Thanks
Tim

Quote
"Dan Palley" <d...@trams.com> wrote in message news:3b7d968f$1_2@dnews...
> You have a couple of options:

> - instead of deleting the client, mark it as inactive, leaving all other
> records intact.  Your reports can ignore inactive clients.

> - Use an ON DELETE SET NULL parameter in your foreign key definition.
This
> will make the foreign key fields NULL which is OK; a table that references
> another table via a foreign key can have a NULL value as the foreign key
> field.

> Dan

> "Tim Chemaly" <tchem...@ing.sun.ac.za> wrote in message
> news:3b7d900c_2@dnews...
> > Hi
> > I'm designing an accounting system using interbase.
> > I have a table of clients with each client assigned a client id which is
> my
> > primary key.
> > Since this primary key will be used as a foreign key in other tables
such
> as
> > purchases by clients I'm in a bit of a paradox:-))

> > My problem is that if I delete a client all subsequent records in the
> > purchases table will be deleted as well. BUT I don't want this because I
> > want to be able to determine all purchases and credits, profits etc.
over
> > the year(s) regardless of whether this client still exists. BUT If the
> > entries aren't deleted from the table how can the foreign key in the
> record
> > still point to an entry in the clients table that does not exist?

> > Can anyone with database experience please help me on this one...I'm
> really
> > stuck.

> > Thanks
> > Tim

Team

Delphi Developer
Wed, 04 Feb 2004 07:41:12 GMT

Re:preventing entries being deleted when primary key is deleted


No. Just the foreign key.

--
Bill
(TeamB cannot answer questions received via email)

Sergio Samayo

Delphi Developer
Wed, 04 Feb 2004 08:16:04 GMT

Re:preventing entries being deleted when primary key is deleted


I think that first approach must be used. You can maintain correct balance
data supported by detailed records but what you will gona do if your user
request something like "from who we get this money" or "to wicth customer we
send this invoice".

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Dan Palle

Delphi Developer
Sat, 07 Feb 2004 00:26:31 GMT

Re:preventing entries being deleted when primary key is deleted


I agree -- this is what we do in our accounting app.

Dan

Quote
"Sergio Samayoa" <serg...@terra.com.gt> wrote in message

news:3b7db2cd_1@dnews...
Quote
> I think that first approach must be used. You can maintain correct balance
> data supported by detailed records but what you will gona do if your user
> request something like "from who we get this money" or "to wicth customer
we
> send this invoice".

> --
> Sergio Samayoa
> Lgica Software
> http://www.geocities.com/logicasw/

Other Threads

1. Delete a record in a TDBGrid with the Delete Key (and not Ctrl-Delete)

2. I am having trouble Deleting with TQuery.

3. TActionList: delete shortcut prevents user from deleting text

4. delete shortcut prevents user from deleting text

5. Deleting Primary key

6. Can not delete primary key

7. Deleting a Tlistview item by pressing Delete key

8. Primary key or not primary key.........

9. Problem deleting the entries from a batch TADODataSet

10. How to delete a entry for a hidden index