Problems getting foreign keys working in MySQL
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 trigge开发者_JS百科r 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;
You need to explicitly declare the foreign key constraint. The REFERENCES
clause on a column definition doesn't work.
Try this:
CREATE TABLE article_section_lt (
fk_article_id INTEGER UNSIGNED NOT NULL,
fk_article_section_id INTEGER UNSIGNED NOT NULL,
KEY fk_article_id (fk_article_id),
CONSTRAINT fk_article_section_lt_to_article FOREIGN KEY (fk_article_id) REFERENCES articles(id) ON DELETE CASCADE
)ENGINE=INNODB;
精彩评论