On delete cascade not deleting the associates entries

Question

I have a marks table which holds the foreign key to an associates message. I used ON DELETE with the expectation that deleting an entry in the marks table would also delete the associated message. It is not working as expected though. I checked out all the other post and can't find any relevant to this one.

I can insert into the database successfully

DELETE FROM marks WHERE x = 37.7836

but when I delete a mark the message that is associated with it is not deleted. Any insights as to what I am doing wrong here?

Edit: Here is an image of me demonstrating my deletion process:

schema

DROP DATABASE uncovery;
CREATE DATABASE uncovery;

SET foreign_key_checks=0;

USE uncovery;

CREATE TABLE marks (
  id int(5) AUTO_INCREMENT,
  x float(10, 6),
  y float(10, 6),
  z float(10, 6),
  timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
  messageId int(5),
  FOREIGN KEY (messageId)
    REFERENCES messages(id)
    ON DELETE CASCADE,
  PRIMARY KEY (id)
);

CREATE TABLE messages (
  id int(5) AUTO_INCREMENT,
  messageString text,
  PRIMARY KEY (id)
);

EDIT 2: Schema Refactor

Here is my newly updated schema:

As you can see I am getting an error that this is invalid when I try to run it:

Answer 1

The foreign key as you've created it won't work the way you expect. Under your schema, if an entry is deleted from messages, it will delete all corresponding entries from the marks table, not the other way around.

Also, you must ensure that you are using the InnoDB storage engine for your tables, or your foreign keys won't work at all. They are not supported in the MyISAM engine type.

To get your schema working such that deleting a mark will cause a message to be deleted, you need to have a markId as a foreign key in the messages table, like so:

CREATE TABLE `messages` (
  `id` int(5) NOT NULL auto_increment,
  `messageString` text,
  `markId` int(5) default NULL,
  PRIMARY KEY  (`id`),
  KEY `markId` (`markId`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`markId`) REFERENCES `marks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `marks` (
  `id` int(5) NOT NULL auto_increment,
  `x` float(10,6) default NULL,
  `y` float(10,6) default NULL,
  `z` float(10,6) default NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;