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?
Delete data from database
Moderator: General Moderators
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
Referential integrity will help you achieve the solution. You have to make one table master and other child and table engine should be InnoDB. If you reference a key of master table (first table) from child table (second table) and put event on child table ON DELTE CASCADE & ON UPDATE CASCADE, you can get the solution.
Sample referential integrity enabled table script :
In the above scripr POS sub category references main category table and if the POS main category is deleted, the effect will be on sub-category.
Hope this will help you.
With Regards,
DIbyendra
Sample referential integrity enabled table script :
Code: Select all
CREATE TABLE `tbl_pos_main_cat` (
`pos_main_cat_id` TINYINT(4) NOT NULL AUTO_INCREMENT,
`pos_main_cat_sf` VARCHAR(255) NOT NULL DEFAULT '',
`pos_main_cat_ff` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`pos_main_cat_id`),
UNIQUE KEY `UNIQUE_tbl_pos_main_cat` (`pos_main_cat_sf`,`pos_main_cat_ff`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Part-of-Speech Main Category';
/*Table structure for table `tbl_pos_sub_cat` */
DROP TABLE IF EXISTS `tbl_pos_sub_cat`;
CREATE TABLE `tbl_pos_sub_cat` (
`pos_sub_cat_id` TINYINT(4) NOT NULL AUTO_INCREMENT,
`pos_main_cat_id` TINYINT(4) NOT NULL,
`pos_sub_cat_sf` VARCHAR(255) NOT NULL DEFAULT '',
`pos_sub_cat_ff` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`pos_sub_cat_id`),
UNIQUE KEY `UNIQUE_tbl_pos_sub_cat` (`pos_main_cat_id`, `pos_sub_cat_sf`, `pos_sub_cat_ff`),
KEY `FK_tbl_pos_sub_cat` (`pos_main_cat_id`),
CONSTRAINT `CONSTRAINT_tbl_pos_sub_cat` FOREIGN KEY(`pos_main_cat_id`) REFERENCES `tbl_pos_main_cat`(`pos_main_cat_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Part-of-Speech Sub-Category Level 1';
Hope this will help you.
With Regards,
DIbyendra