I've been trying to get a delete to cascade and it just doesn't seem to work. I'm sure I am missing something obvious, can anyone help me find it?
I would expect a delete on the 'articles' table to trigger a delete on the corresponding rows in the 'article_section_lt' table.
CREATE TABLE articles (
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
url_stub VARCHAR(255) NOT NULL UNIQUE,
h1 VARCHAR(60) NOT NULL UNIQUE,
title VARCHAR(60) NOT NULL,
description VARCHAR(150) NOT NULL,
summary VARCHAR(150) NOT NULL DEFAULT "",
html_content TEXT,
date DATE NOT NULL,
updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=INNODB;
CREATE TABLE article_sections (
/* blog, news etc */
id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
url_stub VARCHAR(255) NOT NULL UNIQUE,
h1 VARCHAR(60) NOT NULL,
title VARCHAR(60) NOT NULL,
description VARCHAR(150) NOT NULL,
summary VARCHAR(150) NOT NULL DEFAULT "",
html_content TEXT NOT NULL DEFAULT ""
)ENGINE=INNODB;
CREATE TABLE article_section_lt (
fk_article_id INTEGER UNSIGNED NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
fk_article_section_id INTEGER UNSIGNED NOT NULL
)ENGINE=INNODB;
