MySQL - On delete cascade issue

I have 3 table: A,B and C. Relations are:

• A->B 1:1
• A->C 1:n

Table A is the main table:

CREATE TABLE IF NOT EXISTS `A` (
  `_id` INT NOT NULL AUTO_INCREMENT ,
  ..........
  `_id_B` INT NOT NULL ,
  PRIMARY KEY (`_id`) ,
  INDEX `fk_Atable_Btable` (`_id_B` ASC) ,
  CONSTRAINT `fk_A_B`
    FOREIGN KEY (`_id_B` )
    REFERENCES `B` (`_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `B` (
  `_id` INT NOT NULL AUTO_INCREMENT ,
  ........
  P开发者_运维知识库RIMARY KEY (`_id`) )
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `C` (
  `_id` INT NOT NULL ,
  `_id_A` INT NOT NULL ,
  PRIMARY KEY (`_id`) ,
  INDEX `fk_Ctable_Atable` (`_id_A` ASC) ,
  CONSTRAINT `fk_C_A`
    FOREIGN KEY (`_id_A` )
    REFERENCES `A` (`_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

I would to delete an entry on table A and I would delete ON CASCADE the entries from B and C.

How can I set my sql scripts to do it?

---

Hi its similar like following

. I have two tables: article commentary

Commentary has a foreign key to article.id, and of course, when deleting one article, I want all it's comments deleted as well. I've tried to do it as follows:

create table article (
id int not null auto_increment,
title varchar(80) not null,
subtitle varchar(80) not null,
content text not null,
author varchar(40) not null,
time datetime not null,

PRIMARY KEY (id)
);

create table commentary (
id int not null auto_increment,
article_id int not null,
title varchar(30),
content tinytext not null,
author varchar(40) not null,
email varchar(50),
ip varchar(16) not null,
time datetime not null,

PRIMARY KEY (id),
FOREIGN KEY (article_id)
    REFERENCES article(id)
    ON DELETE CASCADE
);

This is according to documentation if I am not mistaken (a poster on the MySQL website with short description

This link may also useful to you

---

You say that tables A and B are in 1:1 relationship but both have auto incrementing key. Instead you have an A._id_B field that I suppose is used to locate the corresponding (1:1) row in table B.

The standard approach would be to drop that A._id_B and in the other table (B), make the primary key not auto incrementing and also make it a FOREIGN KEY to the primary key of A:

CREATE TABLE IF NOT EXISTS A 
( _id INT NOT NULL AUTO_INCREMENT ,
  ..........
  PRIMARY KEY (_id) 
)
ENGINE = InnoDB ;


CREATE TABLE IF NOT EXISTS B 
( _id INT NOT NULL ,
  ........
  PRIMARY KEY (_id) ,                 --- B._id  is a primary key
  CONSTRAINT fk_B_A
    FOREIGN KEY (_id)                 --- and a foreign key
      REFERENCES A (_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE                
 )
ENGINE = InnoDB ;

---

You would need to introduce a foreign key relationship from B pointing back to A (that is, B needs to include a column with A's id) for this to work. Then set your 'ON DELETE' clause for that relationship to 'ON DELETE CASCADE', and similarly for the fk_C_A constraint on C.