delete data from database

Dvorak

Hi,
Let's say i have two tables,

table_one;
userid,userinput,indexno

table_two;
cond,qant,amp,indexno

the indexno in table_one and table_two are the same.if i choose to delete one row of data in table_one,i want the data with the same indexno in table_two be deleted as well.
how can i achieve this?

bretticus

If you are using at least MySQL version 4.0 and your tables are of type innodb, you can use foreign keys with cascading. To alter your current tables you would issue the following queries:

ALTER TABLE `table_one` TYPE = innodb;
ALTER TABLE `table_two` TYPE = innodb;

followed by:

 ALTER TABLE `table_two` ADD FOREIGN KEY ( `indexno` ) REFERENCES `table_one` ( `indexno` ) ON DELETE CASCADE

to turn on cascading.

I believe this means indexnoin table_onehas to be the primary key for indexno to be a foreign key for table_two.

Now, when a record in table_oneis deleted, records with a corresponding indexno in table_twoare automatically cleaned up (deleted.)

Dvorak

i put the code for foreign key and it returns me error...

bretticus

Dvorak wrote:
i put the code for foreign key and it returns me error...

and??????????

Dvorak

bretticus wrote:
and??????????

it doesn't work

bogu

U can do it like this:

DELETE table_one.*, table_two.*
FROM table_one
LEFT JOIN table_two USING(indexno)
WHERE indexno = 'index_value'

U can read more about DELETE Multiple-table syntax here.

bretticus

it doesn't work

You didn't give any error message, you didn't show your table schema or anything. You just said, "waaaa, it doesn't work." Geeessshhh!!! I promise that "it works" if you do it right. But nobody can help you do it right with an explanation like, "it doesn't work."

mborish_2000

What's the deal with this "innoDB" buzzword that keeps showing up? I'm trying to do something similar to this posting and am confused about creating auto increment multi-talbe stuff and creating a query to have it all deleted. Are there multiple MySQL options available? Is one better for LARGE Codd normalization and auto incrementing?

bretticus

I'm not familiar with Codd normalization (nor the definitions of most normal forms for that matter.) The point with using innodb in Dvorak's scenario was to clean up orphan records as innodb is the only MySQL database engine type that I know of that supports cascading deletes. The idea being you delete a record with a primary key and, in other tables, any foreign key records that stem off of that record are automatically deleted also. Sure, you can call multiple delete queries from your PHP code, however, cascading deletes ensure no orphan records (if the developer forgets to delete from such-and-such table.) Also, foreign keys are enforced (you can't create a record with a foreign key that doesn't exist in the primary key table.)