Incorrect options in FOREIGN KEY constraint - mysql

Question

I'm trying to add a self-referencing FOREIGN KEY CONSTRAINT on a table (comment):

SET
FOREIGN_KEY_CHECKS = OFF;

ALTER TABLE
`comment` ADD CONSTRAINT comment_parent_id_foreign FOREIGN KEY(parent_id) REFERENCES `comment`(id) ON DELETE CASCADE;

And I get this error:

#1825 - Failed to add the foreign key constraint on table 'comment'. Incorrect options in FOREIGN KEY constraint 'databasename/comment_parent_id_foreign'

Earlier I accidentally added this constraint on an unintended table (post). I wasn't able to delete the constraint, so I removed the column (parent_id) itself. I created this column again. And now I'm not able to add this self-referencing constraint.

Both have same datatype bigint(20) (id and parent_id) and commentid is a primary key

Table is using InnoDB engine

Here's my table structure:

thanks

I don't really know if you really had named your table `comment` with `\`` quote, probably it would works better without them : `ALTER TABLE comment ADD CONSTRAINT comment_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES comment (id) ON DELETE CASCADE;` — damadam, Nov 26 '19 at 10:11

score 2 · Answer 1 · edited Dec 15 '22 at 14:26

2

1.Engine should be the same e.g. InnoDB

2.Datatype should be the same, and with same length. e.g. VARCHAR(50)

3.Collation Columns charset should be the same. e.g. utf-8

4.Unique - Foreign key should refer to field that is unique in the reference table

5.Nullable If the constraint's action is SET NULL, make sure that the column is nullable.

edited Dec 15 '22 at 14:26

stollr

6,534
4
43
59

answered Nov 26 '19 at 10:05

Ramki

452
2
16

ALTER TABLE comment ADD CONSTRAINT fk_comment_parent_id FOREIGN KEY (parent_id) REFERENCES comment(id); – Ramki Nov 26 '19 at 10:12
can you try this @JayWadhwa – Ramki Nov 26 '19 at 10:13

score 2 · Accepted Answer · answered Nov 26 '19 at 10:23

2

' The size and sign of integer types must be the same' - dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html - change so that both are unsigned.

answered Nov 26 '19 at 10:23

P.Salmon

17,104
2
12
19

score 1 · Answer 3 · answered Apr 08 '22 at 12:15

1

For those coming from Google: If you have this in your query, ensure that the column is nullable.

ON DELETE SET NULL

answered Apr 08 '22 at 12:15

John Muraguri

426
7
6

score 0 · Answer 4 · answered Nov 26 '19 at 10:20

0

Try this :

ALTER TABLE comment ADD CONSTRAINT fk_comment_parent_id FOREIGN KEY (parent_id) REFERENCES comment(id);

answered Nov 26 '19 at 10:20

Ramki

452
2
16

score 0 · Answer 5 · answered Dec 15 '22 at 14:31

0

If you do not find the reason for the error, try this to get more details about the error:

SHOW ENGINE INNODB STATUS

And in the output search for LATEST FOREIGN KEY ERROR.

Credits go to @sidonai.

answered Dec 15 '22 at 14:31

stollr

6,534
4
43
59

Incorrect options in FOREIGN KEY constraint - mysql

5 Answers5