Foreign Key accepted but not working

Question

I'm creating a 'polls' plugin for my website, and for this I have 2x tables (shown below).

What I'm trying to achieve is that by delete a 'poll' from the dd_polls table, any linked foreign keys on the dd_poll_options table are also deleted.

Both tables create just fine, but my expectation as mentioned above is not happening.

Am I using foreign keys in the correct way, and if so, how can my code be fixed? Thanks.

CREATE TABLE dd_polls (
    ID smallint(3) NOT NULL AUTO_INCREMENT,
    poll_created_by smallint(3) DEFAULT "0",
    poll_created_date datetime DEFAULT "0000-00-00 00:00:00" NOT NULL,
    poll_last_edited_by smallint(3) DEFAULT "0",
    poll_last_edited_date datetime DEFAULT "0000-00-00 00:00:00" NOT NULL,
    poll_title varchar(128) COLLATE latin1_general_ci,
    poll_start_date date DEFAULT "0000-00-00" NOT NULL,
    poll_expiry_date date DEFAULT "0000-00-00" NOT NULL,
    poll_closed tinyint(1) NOT NULL,
    poll_allow_recasting tinyint(1) NOT NULL,
    poll_show_votes tinyint(1) NOT NULL,
    UNIQUE (ID)
)

CREATE TABLE dd_poll_options (
    ID smallint(3) NOT NULL AUTO_INCREMENT,
    option_text text(255) COLLATE latin1_general_ci,
    option_order smallint(2) DEFAULT "0",
    poll_id smallint(3) NOT NULL,
    FOREIGN KEY (poll_id) REFERENCES dd_polls (ID)
        ON DELETE CASCADE,
    UNIQUE (ID)
)

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

Just a wild guess, but I think you need to use the InnoDB engine for this to work. I bet you are using MyISAM.

Here is another question with the same symptoms: on-delete-cascade-not-working-in-mysql

And isn't the correct syntax

CONSTRAINT FOREIGN KEY (poll_id) REFERENCES dd_polls(ID) ON DELETE CASCADE,

?

edited May 23 '17 at 12:28

Community

1
1

answered Oct 16 '13 at 15:29

mwhs

5,878
2
28
34

`CONSTRAINT` is optional but I agree on the rest, it's probably MyISAM. – ypercubeᵀᴹ Oct 16 '13 at 15:51
The type is indeed `MyISAM` on those two tables. I'll look for a way of creating them as InnoDB and try again. Thanks. – David Gard Oct 16 '13 at 15:55
@David: also look at the link I posted in my answer. There is an easy way to switch the storage engine to InnoDB per table on the fly: ALTER TABLE xxxx ENGINE = InnoDB; – mwhs Oct 16 '13 at 15:55
@mwhs - Thank you so much. `ALTER TABLE xxxx ENGINE = InnoDB` worked like a charm and now when I delete a poll, the options linked to it in another table are deleted. Thanks also for the link, I'll be sure to create tables using the `InnoDB` engine in the future. – David Gard Oct 17 '13 at 11:40

score -1 · Answer 2 · edited Oct 17 '13 at 18:49

-1

ALTER TABLE `dd_polls` ADD FOREIGN KEY ( `poll_id`, `id` ) 
REFERENCES `dd_poll_options` ( `poll_id`, `id`) 
ON DELETE RESTRICT ON UPDATE RESTRICT ;

edited Oct 17 '13 at 18:49

Mihai

26,325
7
66
81

answered Oct 16 '13 at 15:39

Hituptony

2,740
3
22
44

Foreign Key accepted but not working

2 Answers2