 |
|
|
|
Next: newbie JDBC SP exec
|
| Author |
Message |
External
Since: Aug 19, 2003
Posts: 49
|
(Msg. 1) Posted: Mon Mar 15, 2004 3:30 pm
Post subject: Basic Question about DELETE
Archived from groups: mailing>database>mysql, others (more info?)
|
|
|
Just a basic question regarding a relational database like MySQL.
I am using a MySQL database wherein tables are, naturally, linked to other
tables via indexes. Say I have tableA and tableB. TableB has a field which
contains a link to tableA.
Each day, operations are performed wherein a third table, tableC, is
created, with a history of the days actions, and thus contains links to the
other tables.
Now, if I delete a record in tableA dont I destroy the integrity of the
database? Or, to a lesser extent, if I destroy a link in tableB also. How
does one maintian the integrity of the database while allowing records to be
deleted? How do you typically do that?
Thanks, Ike |
|
| Back to top |
|
 | |
External
Since: Sep 10, 2003
Posts: 41
|
(Msg. 2) Posted: Mon Mar 15, 2004 4:51 pm
Post subject: Re: Basic Question about DELETE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Ike" wrote in message
> Just a basic question regarding a relational database like MySQL.
>
> I am using a MySQL database wherein tables are, naturally, linked to other
> tables via indexes. Say I have tableA and tableB. TableB has a field
which
> contains a link to tableA.
>
> Each day, operations are performed wherein a third table, tableC, is
> created, with a history of the days actions, and thus contains links to
the
> other tables.
>
> Now, if I delete a record in tableA dont I destroy the integrity of the
> database? Or, to a lesser extent, if I destroy a link in tableB also. How
> does one maintian the integrity of the database while allowing records to
be
> deleted? How do you typically do that?
>
> Thanks, Ike
>
>
Three options:
1=set the referring column from all referring rows to NULL before deleting
2=delete all referring rows before deleting
3=do not delete |
|
| Back to top |
|
| |
External
Since: Jan 12, 2004
Posts: 11
|
(Msg. 3) Posted: Mon Mar 15, 2004 7:36 pm
Post subject: Re: Basic Question about DELETE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Silvio Bierman wrote:
> Three options:
>
> 1=set the referring column from all referring rows to NULL before deleting
> 2=delete all referring rows before deleting
> 3=do not delete
4=delete with option "on cascade"
A.G. >> Stay informed about: Basic Question about DELETE |
|
| Back to top |
|
| |
External
Since: Mar 16, 2004
Posts: 3
|
(Msg. 4) Posted: Tue Mar 16, 2004 6:19 am
Post subject: Re: Basic Question about DELETE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Andree Große wrote ...
> Silvio Bierman wrote:
> > Three options:
> >
> > 1=set the referring column from all referring rows to NULL before deleting
> > 2=delete all referring rows before deleting
> > 3=do not delete
>
> 4=delete with option "on cascade"
Unfortunately, MySQL doesn't yet support cascading deletes, so Ike will
have to follow Silvio's original suggestions.
David Harper
Cambridge, England >> Stay informed about: Basic Question about DELETE |
|
| Back to top |
|
| |
External
Since: Sep 10, 2003
Posts: 41
|
(Msg. 5) Posted: Tue Mar 16, 2004 1:13 pm
Post subject: Re: Basic Question about DELETE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Andree Große" wrote in message
> Silvio Bierman wrote:
> > Three options:
> >
> > 1=set the referring column from all referring rows to NULL before
deleting
> > 2=delete all referring rows before deleting
> > 3=do not delete
>
> 4=delete with option "on cascade"
>
> A.G.
Very non-standard but if available equivalent to 2.
Silvio Bierman >> Stay informed about: Basic Question about DELETE |
|
| Back to top |
|
| |
External
Since: Mar 16, 2004
Posts: 1
|
(Msg. 6) Posted: Tue Mar 16, 2004 7:25 pm
Post subject: Re: Basic Question about DELETE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
In article ,
devnull.TakeThisOut@obliquity.u-net.com (David Harper) writes:
>> Silvio Bierman wrote:
>> > Three options:
>> >
>> > 1=set the referring column from all referring rows to NULL before deleting
>> > 2=delete all referring rows before deleting
>> > 3=do not delete
>>
>> 4=delete with option "on cascade"
> Unfortunately, MySQL doesn't yet support cascading deletes, so Ike will
> have to follow Silvio's original suggestions.
From the fine MySQL manual:
Starting from version 3.23.50, you can also associate the `ON DELETE
CASCADE' or `ON DELETE SET NULL' clause with the foreign key
constraint. Corresponding `ON UPDATE' options are available starting
from 4.0.8. If `ON DELETE CASCADE' is specified, and a row in the
parent table is deleted, then InnoDB automatically deletes also all
those rows in the child table whose foreign key values are equal to the
referenced key value in the parent row. >> Stay informed about: Basic Question about DELETE |
|
| Back to top |
|
| |
External
Since: Jan 25, 2004
Posts: 32
|
(Msg. 7) Posted: Tue Mar 16, 2004 10:26 pm
Post subject: Re: Basic Question about DELETE [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Harald Fuchs wrote:
[SNIP]
>>Unfortunately, MySQL doesn't yet support cascading deletes, so Ike will
>>have to follow Silvio's original suggestions.
>
>
> From the fine MySQL manual:
>
> Starting from version 3.23.50, you can also associate the `ON DELETE
> CASCADE' or `ON DELETE SET NULL' clause with the foreign key
> constraint. Corresponding `ON UPDATE' options are available starting
> from 4.0.8. If `ON DELETE CASCADE' is specified, and a row in the
> parent table is deleted, then InnoDB automatically deletes also all
> those rows in the child table whose foreign key values are equal to the
> referenced key value in the parent row.
This is correct for InnoDB tables, but not for MyISAM, which is the
default table type. If Ike's application demands referential integrity,
he should make sure that he is using InnoDB tables.
David Harper
Cambridge, England >> Stay informed about: Basic Question about DELETE |
|
| Back to top |
|
| |
| Related Topics: | delete where exists ??? - Hi, if i run the following query: select * from std_order_lines sol where exists(select * from std_orders so, customers c where so.customers_id = c.customers_id and c.test_customer =....
delete dupes with a unique id using a temp table - I need to delete the dupes across bu's. The records are unique based on bu+ndc but there are 400+ duplicates.. There is a sequential id that is unique. I am trying to remove these 400+ records. 1. SELECT * FROM ci GROUP BY bu,ndc HAVING..
Question about UPDATE and LEFT JOIN - Hi, I hope this is the right place to post, but, I am having a problem with an UPDATE command and a LEFT JOIN, I am using something like: UPDATE table_a LEFT JOIN table_b ON table_a.field1=table_b.field1 SET table_b.field6='1' WHERE table_a.field2='1'; ...
Join and limit the result question - Hi guys! I have a challenge that I hope you can help me with. I have three tables and I have exported them with some sample data and uploaded them here: http://server.arton.no/filesharing/SQL.zip Here is how the tables looks like:..
Best way to issue hundreds of inserts/updates??? - Using mysql 4.0.23- What is the best way to execute several (hundreds of) inserts and updates? Rather than issuing tons of individual inserts and updates, can I send the strings to a text file and then have mysql do them all?? IE : query.txt insert.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
