CODEWITHSUNDEEP
mysql
Peanut
Peanut

Reputation: 3295

Error 1215 foreign key cant be added

Ok they are the same datatype and everything before someone comments this!

On user table:

group_id TINYINT(3) UNSIGNED NOT NULL DEFAULT 1

On the user_groups table:

id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT KEY

Then my sql statement:

ALTER TABLE user

ADD CONSTRAINT FK_user_group_id_user_groups_id
FOREIGN KEY (group_id) REFERENCES user_groups (id)
ON UPDATE CASCADE
ON DELETE SET NULL

I don't know if it is because I'm setting it null on delete, but if I delete the user_group, I DONT want to delete the user. Just set the group_id to null or.. I don't know. It was working when I had the user group_id column like this:

group_id TINYINT(3) UNSIGNED NULL DEFAULT NULL

But I want all user's default group as 1 (as that is the basic user group).

Thanks for any help.

Upvotes: 0

Views: 57

Answers (1)

Marc B
Marc B

Reputation: 360702

Yes, it's because of the ON DELETE SET NULL. You've defined the FK field as NOT NULL, which conflicts with the FK definition - delete operations are told to set the field to null, but the field is defined as not null, so...

MySQL does "support" ON DELETE SET DEFAULT, but only by accepting the syntax, and not supporting the actual default operation.

You might have to make the field nullable, and set a trigger to reset the field to your desired default value when it does get nulled.

Upvotes: 1

Related Questions