CODEWITHSUNDEEP
mysqldatabaseforeign-keysconstraintsmariadb
Musterknabe
Musterknabe

Reputation: 6081

Cannot add foreign key with "set default" as on delete

I'm using MariaDB and now wanted to add a foreign key to my database table. I'm having problems when trying to use set default as my on delete constraint;=. (The default of the user_id in the pages table is 1)

`alter table `pages` add constraint pages_user_id_foreign foreign key (`user_id`) references `users` (`id`) on delete set default`

However, when I'm doing this, I'm getting the error

SQLSTATE[HY000]: General error: 1005 Can't create table 'project.#sql-add_38' (errno: 150)

When I look at the last foreign key error, I'm getting the following output

160322 12:40:56 Error in foreign key constraint of table project.pages: Alter table project.pages with foreign key constraint failed. Parse error in ' foreign key (user_id) references users (id) on delete set default' near ' on delete set default'.

When I do it without on delete set default it works without any problems

`alter table `pages` add constraint pages_user_id_foreign foreign key (`user_id`) references `users` (`id`)`

Same goes, if I use set null for my on delete condition

alter table `pages` add constraint pages_provider_id_foreign foreign key (`provider_id`) references `providers` (`id`) on delete cascade

Somehow, only the set default doesn't work, but the error message doesn't give me enough information

Upvotes: 3

Views: 1221

Answers (1)

Musterknabe
Musterknabe

Reputation: 6081

Ok, got it. Seems like InnoDB doesn't allow for ON DELETE SET DEFAULT

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Upvotes: 2

Related Questions