MySQL - Cannot add or update a child row

mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

ERROR 1452 (23000) at line 8613: Cannot add or update a child row: a foreign key constraint fails

Line 8613: INSERT INTO puffle_item (id, parent_id, name, type, play_external, cost, quantity, member, food_effect, rest_effect, play_effect, clean_effect) VALUES

Table it's referring to:

DROP TABLE IF EXISTS puffle_item;
CREATE TABLE puffle_item (
  id INT NOT NULL,
  parent_id INT NOT NULL,
  name VARCHAR(50) NOT NULL DEFAULT '',
  type VARCHAR(10) NOT NULL DEFAULT 'care',
  play_external VARCHAR (10) NOT NULL DEFAULT 'none',
  cost INT NOT NULL DEFAULT 0,
  quantity SMALLINT NOT NULL DEFAULT 1,
  member BOOLEAN NOT NULL DEFAULT FALSE,
  food_effect SMALLINT NOT NULL DEFAULT 0,
  rest_effect SMALLINT NOT NULL DEFAULT 0,
  play_effect SMALLINT NOT NULL DEFAULT 0,
  clean_effect SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  CONSTRAINT puffle_item_ibfk_1 FOREIGN KEY (parent_id) REFERENCES puffle_item (id) ON DELETE CASCADE ON UPDATE CASCADE
);

Example of item trying to put in the table: (1, 1, 'Brush', 'care', 'none', 0, 1, FALSE, -2, -2, 5, 5),

Any help?

Solution

you have to disable the constraint, because parent id 1 doesn't exist, at the time you try to enter that row.

CREATE TABLE puffle_item (
  id INT NOT NULL,
  parent_id INT NOT NULL,
  name VARCHAR(50) NOT NULL DEFAULT '',
  type VARCHAR(10) NOT NULL DEFAULT 'care',
  play_external VARCHAR (10) NOT NULL DEFAULT 'none',
  cost INT NOT NULL DEFAULT 0,
  quantity SMALLINT NOT NULL DEFAULT 1,
  member BOOLEAN NOT NULL DEFAULT FALSE,
  food_effect SMALLINT NOT NULL DEFAULT 0,
  rest_effect SMALLINT NOT NULL DEFAULT 0,
  play_effect SMALLINT NOT NULL DEFAULT 0,
  clean_effect SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  CONSTRAINT puffle_item_ibfk_1 FOREIGN KEY (parent_id) REFERENCES puffle_item (id) ON DELETE CASCADE ON UPDATE CASCADE
);

SET FOREIGN_KEY_CHECKS=0;

INSERT INTO puffle_item (id, parent_id, name, type, play_external, cost, quantity, member, food_effect, rest_effect, play_effect, clean_effect) VALUES (1, 1, 'Brush', 'care', 'none', 0, 1, FALSE, -2, -2, 5, 5);

SET FOREIGN_KEY_CHECKS=1;

select * from puffle_item

id | parent_id | name  | type | play_external | cost | quantity | member | food_effect | rest_effect | play_effect | clean_effect
-: | --------: | :---- | :--- | :------------ | ---: | -------: | -----: | ----------: | ----------: | ----------: | -----------:
 1 |         1 | Brush | care | none          |    0 |        1 |      0 |          -2 |          -2 |           5 |            5

db<>fiddle here