Getting a "foreign key constraint fails" even though I have "on delete cascade"

Question

I thought the point of ON DELETE CASCADE was that this wouldn't happen. :\ I have the following tables:

CREATE TABLE Tweets (
    tweetID INTEGER                 NOT NULL AUTO_INCREMENT,
    userID INTEGER                  NOT NULL,
    content VARCHAR(140)            NOT NULL,
    dateTime TIMESTAMP              NOT NULL DEFAULT CURRENT_TIMESTAMP,
    hasPoll INTEGER                 NOT NULL,
    visible INTEGER                 NOT NULL DEFAULT 1,
    PRIMARY KEY (tweetID),
    FOREIGN KEY (userID) REFERENCES Users(userID)
    ON DELETE CASCADE
);

CREATE TABLE Polls (
    pollID INTEGER NOT NULL AUTO_INCREMENT,
    tweetID INTEGER NOT NULL,
    pollOptionText VARCHAR(300),
    PRIMARY KEY (pollID),
    FOREIGN KEY (tweetID) REFERENCES Tweets(tweetID)
);

The problem is that when I try to delete a Tweet which has a Poll attached to it, I get the following error (via Flask):

_mysql_exceptions.IntegrityError
IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`twitter`.`polls`, CONSTRAINT `polls_ibfk_1` FOREIGN KEY (`tweetID`) REFERENCES `Tweets` (`tweetID`))')

Help please!

Answer 1

That is indeed the point of on delete cascade. You get the error, because your code doesn't declare on delete cascade from "poll" to "tweet".

CREATE TABLE Polls (
    pollID INTEGER NOT NULL AUTO_INCREMENT,
    tweetID INTEGER NOT NULL,
    pollOptionText VARCHAR(300),
    PRIMARY KEY (pollID),
    FOREIGN KEY (tweetID) REFERENCES Tweets(tweetID)
      ON DELETE CASCADE
);

This will delete rows in "Polls" when corresponding rows are deleted in "Tweets".

Answer 2

You have to put ON DELETE CASCADE after FOREIGN KEY (tweetID) REFERENCES Tweets(tweetID)

According to the MySQL Foreign Key Constraints reference:

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.

Also, according to the MySQL Foreign Keys reference:

For storage engines other than InnoDB, it is possible when defining a column to use a REFERENCES tbl_name(col_name) clause, which has no actual effect, and serves only as a memo or comment to you that the column which you are currently defining is intended to refer to a column in another table.

So since the foreign key is from the child table to the parent table, it makes foo a parent table and Polls a child table, so deleting a row from Tweets will cascade deletions to Pools, providing you use InnoDB or some other storage engine that supports it.

UPDATE:

This error is because you have a relation between poll and twitter... without cascading you have to delete or update the polls removing the relation with the Tweet that will be deleted. Or use ON DELETE CASCADE:

CREATE TABLE Tweets (
    tweetID INTEGER                 NOT NULL AUTO_INCREMENT,
    content VARCHAR(140)            NOT NULL,
    PRIMARY KEY (tweetID)
);

CREATE TABLE Polls (
    pollID INTEGER NOT NULL AUTO_INCREMENT,
    tweetID INTEGER NOT NULL,
    pollOptionText VARCHAR(300),
    PRIMARY KEY (pollID),
    FOREIGN KEY (tweetID) REFERENCES Tweets(tweetID) 
        ON DELETE CASCADE
);

INSERT INTO Tweets VALUES(1,'tweet');
INSERT INTO Polls VALUES(1,1,"pool");
DELETE FROM Tweets WHERE tweetID = 1;