Problems getting foreign keys working in MySQL
I am trying to remove the cascade and it just doesn't work. I'm sure I'm missing something obvious, can anyone help me find it?
I would expect the deletion in the "articles" table to trigger the deletion on the appropriate 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;
+2
a source to share
1 answer
You need to explicitly declare the foreign key constraint. The clause REFERENCES
in the column definition does not work.
Try the following:
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;
+3
a source to share