Problems getting foreign keys working in MySQL

Posted by thehuby on Stack Overflow See other posts from Stack Overflow or by thehuby
Published on 2010-04-13T22:57:58Z Indexed on 2010/04/14 0:03 UTC
Read the original article Hit count: 302

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;

© Stack Overflow or respective owner

Related posts about mysql

Related posts about innodb