CODEWITHSUNDEEP
mysqlsqlforeign-keys
user3111627
user3111627

Reputation: 5

foreign key constraint is incorrectly formed [same table]

here is my table structure

I would like that parent_forum would save an id of parent forum, which is the ID of the same table id column value. As you can see both columns have the same type. My table engine is InnoDB, I try the following query to add a constraint.

ALTER TABLE `forums` ADD CONSTRAINT `parent_forum constraint` FOREIGN KEY (`id`) REFERENCES `codeigniter`.`forums`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

and I get an error that is written on the title. What is actually wrong here? Category_id successfully works with other table ID value.

Upvotes: 0

Views: 300

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think this is the syntax you are looking for:

ALTER TABLE forums ADD CONSTRAINT parent_forum_constraint
    FOREIGN KEY (parent_forum) REFERENCES codeigniter.forums(id)
        ON DELETE RESTRICT ON UPDATE RESTRICT;

The column in parentheses is the one that refers to the column after the references. Also, don't put spaces in names, unless you have a really good reason. Code is much more readable without all the backticks.

Upvotes: 1

Related Questions