CODEWITHSUNDEEP
mysqldatabasecascade
Joshua Crotts
Joshua Crotts

Reputation: 69

Why is my MySQL cascade deletion not working?

I have three tables: Employee, Dependent, and DependsOn. For some reason, on my server, the MySQL code isn't doing the cascade deletions correctly. I insert an Employee, a Dependent, then link them in DependsOn. If I delete the Employee, it should delete the corresponding row in DependsOn. However, this is not the case. If I run the query in SQLFiddle.com, it works fine. Why is this not working?

CREATE TABLE Employee 
( 
  SSN int(9) not null unique, 
  First_Name varchar(16) default null, 
  Middle_Initial char(1) default null, 
  Last_Name varchar(16) default null, 
  Address varchar(64) default null, 
  Phone_Number char(10) default null, 

  PRIMARY KEY(SSN) 
);

CREATE TABLE Dependent
(
  Dependent_Name varchar(32) NOT NULL,
  Dependent_Relationship varchar(32) NOT NULL,

  KEY(Dependent_Name),
  KEY(Dependent_Relationship)
);

CREATE TABLE DependsOn
(
  E_SSN int(9) NOT NULL UNIQUE,
  D_Name varchar(32) NOT NULL,
  D_Relationship varchar(32) NOT NULL,

  FOREIGN KEY(E_SSN) REFERENCES Employee(SSN) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(D_Name) REFERENCES Dependent(Dependent_Name) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(D_Relationship) REFERENCES Dependent(Dependent_Relationship) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO Employee VALUES(111223333, "John", "A", "Doe", NULL, NULL);
INSERT INTO Dependent VALUES("Albert A Doe", "Son");
INSERT INTO DependsOn(111223333, "Albert A Doe", "Son");

Upvotes: 1

Views: 53

Answers (1)

Uueerdo
Uueerdo

Reputation: 15951

The MyISAM storage engine permits FOREIGN KEY constraint definitions in CREATE statements, but ignores them. Changing the table engine, to one that supports such constraints such as InnoDB, after the table has already been created does not retroactively add the constraints. The table must be either dropped and re-created, or to preserve the data, the constraints themselves can be added.

Upvotes: 1

Related Questions