MySql Update and Set null to field with foreign key to other table

Question

I have tables like this:

Notes
ID | NAME | CATEGORY
1  | test | 1
2  | test2| 2

Notes Category
ID | NAME | COUNT
1  | tCat | 1
2  | tCat2| 1

And I have foreign key CATEGORY <-> Notes Category.ID

I try update first record in Notes and set the null as CATEGORY because I want to have notes without category and I get errror:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

Do you have a "NOT NULL" constraint on your Notes CATEGORY column? If so, that might be the reason. — Michael Krause, Feb 12 '15 at 16:30
Are [these answers](http://stackoverflow.com/questions/2366854/can-table-columns-with-a-foreign-key-be-null) what you are looking for? — , Feb 12 '15 at 16:36
Well, if I had to guess, it would be that you're trying to update the Notes table with a CATEGORY value that does not correspond to any ID in the Notes Category table. — Michael Krause, Feb 12 '15 at 16:39
yes but if i try write query in phpmyadmin and use SET CATEGORY = NULL it's works — Krzyś Iu, Feb 12 '15 at 16:42

score 0 · Answer 1 · answered Feb 12 '15 at 16:46

0

ALTER TABLE `Notes` DROP FOREIGN KEY Notes_ibfk_1;
UPDATE `Notes` SET CATEGORY = NULL WHERE ID = 1;
ALTER TABLE `Notes` ADD FOREIGN KEY -- http://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

answered Feb 12 '15 at 16:46

danielsos2

11
3

score 0 · Answer 2 · answered Feb 12 '15 at 16:53

0

Try specifically declaring CATEGORY as INT NULL in the NOTES table

answered Feb 12 '15 at 16:53

scubasteve623

637
4
7

MySql Update and Set null to field with foreign key to other table

2 Answers2