0

I have 'parent' and 'child' tables. There is a column 'parent_id' in 'child' table and it is a foreign key. 'id' column in 'parent' table is NOT NULL and INT. 'parent_id' is also INT but DEFAULT NULL. So when I add a new row to 'child' with NULL 'parent_id' I get an error message:

Cannot add or update a child row: a foreign key constraint fails (db.child, CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

CREATE DATABASE db;
USE db;

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT NULL, 
                    parent_id INT NULL DEFAULT NULL,
                    CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB;


INSERT INTO child (id, parent_id) VALUES (1, NULL);
Yelnar
  • 674
  • 1
  • 7
  • 17
  • Please add the `create table` statements and the `insert` statements you use. –  Aug 13 '14 at 07:31
  • Works for me: http://sqlfiddle.com/#!2/31dbe6/1 –  Aug 13 '14 at 08:01
  • Very strange, because when I do the same thing in phpmyadmin I get a message 'Error in Processing Request', despite the row is being inserted. – Yelnar Aug 13 '14 at 08:28
  • That sounds more like a phpMyAdmin bug/problem. Why don't you try a different SQL client? –  Aug 13 '14 at 08:39
  • Actually, I have a web app on php that is connected to my database. And I have a form to add a child. And I get an error when I submit with NULL 'parent_id'. So maybe it is not phpmyadmin bug. – Yelnar Aug 13 '14 at 08:56

2 Answers2

1

Solved. Problem was in php code that I use. There was no problem in sql statements. Thanks everyone!

Yelnar
  • 674
  • 1
  • 7
  • 17
0

You can use

ON DELETE SET NULL ON UPDATE NO ACTION

MangduYogii
  • 935
  • 10
  • 24