in mysql, on delete cascade not working

Question

similar to ON DELETE CASCADE not working in MySQL, but something is not right:

The ANSI Way

-- test delete cascade
CREATE TABLE t1(
    id SERIAL PRIMARY KEY,
    data TEXT
);

CREATE TABLE t2(
    id INT PRIMARY KEY REFERENCES t1(id) ON DELETE CASCADE,
    data2 TEXT
);

INSERT INTO t1 VALUES(1, 'one');
INSERT INTO t2 VALUES(1, 'first');

DELETE FROM t1;
SELECT * FROM t2; -- should have not rows - have one!

use this all the time in postgres, but for some reason cannot get it going in mysql.

---

I am slowly learning, there is the ansi-standard, postgreql way, and there is the mysql way. Each time I think I have somewhat appreciated the difference, I haven't come close.

The MySQL Way

CREATE TABLE `t2` (
    `id` BIGINT(20) UNSIGNED NOT NULL,
    `data2` TEXT,
    PRIMARY KEY (`id`),
    CONSTRAINT `FK_t2_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

To me, the code I have is ansi standard, makes perfect sense, and is (as far as SQL goes) aesthetically pleasing, whereas, the mysql way (thanks for the help!) reminds me of Visual Basic or something - it's really ugly as sin and imho it's wrong to ask intelligent people to debase themselves to write such a thing.

I apologize if ranting, and justly deserve any number of negative ratings. You guys who write this code with ease have my greatest respect. I just hate to see this sort of meaningless punishment inflicted on friends ;-)

Answer 1

This happens because the default storage engine 'MyISAM' does not support foreign keys!

Simply set the engine to InnoDB and make the referencing and referenced columns definition of both tables are matched.

And this is an example:

CREATE TABLE `students` (
  `id` INT AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `age` TINYINT NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB CHARSET=latin1;

CREATE TABLE `marks` (
  `student_id` INT PRIMARY KEY,
  `mark` DECIMAL(5,2) NOT NULL,
  CONSTRAINT marks_FK_1 FOREIGN KEY(`student_id`) REFERENCES students(`id`) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=latin1;

Answer 2

If you create t2 like this it works fine:

CREATE TABLE  `t2` (
  `id` bigint(20) unsigned NOT NULL,
  `data2` text,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_t2_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ETA, in answer to concerns about ugly code, the below also works:

CREATE TABLE  t2 (
  id bigint(20) unsigned NOT NULL PRIMARY KEY,
  data2 text,
  CONSTRAINT  FOREIGN KEY (id) REFERENCES t1(id) ON DELETE CASCADE
) ENGINE=InnoDB ;

The main difference is that the data type for t2.id must match that of t1.id and constraints have to be declared after the columns.