Delete data from database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Locked
User avatar
Dvorak
Forum Newbie
Posts: 11
Joined: Tue Nov 07, 2006 2:25 am

Delete data from database

Post by 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?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

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 :

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';
 
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
Locked