0

I have Three tables 1) product, 2)product_x, 3) product_y. I have set primary key for these three tables. The tables are

1) product : id, name, product_type, created_at 2) product_x : id, product_id,description, created_at 3) product_y : id,product_id,description, created_at

The product_id table in the product_x and product_y tables are foreign reference from the table product. If product_type is=1 entry will go to product_x and if product_type=0 entry will go to product_1. SO here my problem is i have set delete on cascade for the foreign key reference for these two tables. But when i delete an entry from product_x or product_y the corresponding id from the product table is not deleted. That means delete on cascade in not working. I need help from you guys, Please help.

Here is my product table.

--
-- Table structure for table `product`
--

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `product_type` tinyint(4) NOT NULL COMMENT '1=pronova product,2=doctor product',
  `ingredients` varchar(200) NOT NULL,
  `directions` varchar(200) NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '0=inactive,1=active',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;

my product_x table

--

-- Table structure for table product_x

CREATE TABLE IF NOT EXISTS `product_x` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `inventory_id` int(11) NOT NULL,
  `doctor_id` int(11) NOT NULL,
  `stock` varchar(20) NOT NULL,
  `image` varchar(50) NOT NULL,
  `small_image` varchar(100) NOT NULL,
  `sail_price` float DEFAULT NULL,
  `acquire_price` float DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

and my product_y table is 

CREATE TABLE IF NOT EXISTS `product_y` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `inventory_id` int(11) NOT NULL,
  `specialization_type` int(11) NOT NULL,
  `stock` varchar(20) NOT NULL,
  `image` varchar(100) NOT NULL,
  `unit_credit_value` int(11) NOT NULL,
  `suggested_price` float NOT NULL,
  `list_price` float DEFAULT NULL COMMENT 'the price which this product sold if pronova sold this',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `inventory_id` (`product`),
  KEY `FK50E07CF68B1B2BCE` (`inventory_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
anoop
  • 1,604
  • 6
  • 24
  • 50
  • 1
    If you could post your `CREATE TABLE` statements it would probably help. – zombat Jan 17 '12 at 06:14
  • Please show the outputs of these statements - `SHOW CREATE TABLE product;`, `SHOW CREATE TABLE product_x;`, `SHOW CREATE TABLE product_y;` – Abhay Jan 17 '12 at 07:15
  • @anoopkattodi, the table product_x has an index on column "product" but there is no such column in the table. Similarly for product_y. Also, which specific column in product_x and product_y refer to the product id? – Abhay Jan 17 '12 at 09:53

1 Answers1

0

Check to make sure that you are using InnoDB and not MyISAM, if you really don't have control over it, you could write a trigger as explained below:

How to use delete cascade on MySQL MyISAM storage engine?

Community
  • 1
  • 1
Andreas Wong
  • 59,630
  • 19
  • 106
  • 123