2

1452 - Cannot add or update a child row: a foreign key constraint fails (projectphp1707.#sql-e6c_cd, CONSTRAINT #sql-e6c_cd_ibfk_1 FOREIGN KEY (order_id) REFERENCES tbl_order (order_id))

table 1: enter image description here

table 2: enter image description here

CREATE TABLE `tbl_order` (
  `order_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `cus_fullname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `total_price` double DEFAULT NULL,
  `active` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `tbl_payment` (
  `pay_id` int(50) NOT NULL,
  `pro_id` int(15) NOT NULL,
  `pay_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_email` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_adress` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_cardname` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pay_cvc` int(3) NOT NULL,
  `pay_number` text COLLATE utf32_unicode_ci NOT NULL,
  `pay_mm` int(2) NOT NULL,
  `pay_yyyy` int(4) NOT NULL,
  `pay_totals` varchar(255) COLLATE utf32_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf32 COLLATE=utf32_unicode_ci;
Honda hoda
  • 481
  • 2
  • 6
  • 12
  • 1
    I usually see this error because there is a child row referring to a parent row which no longer exists. But given the lack of data in your question, we can't be sure. – Tim Biegeleisen Jan 08 '18 at 02:52
  • 1
    Could you include the operation you tried to perform? It's not clear that this isn't the expected behavior if, for example, you try to insert a row in table 1 without a preexisting row with a corresponding order_id in Table 2. – Joshua R. Jan 08 '18 at 02:58

1 Answers1

7

That happens in one of two ways:

1- You're trying to add a record to the child table and you used a value for the foreign key that doesn't exist in the parent table's primary key.

In this case, double check that the value exists in the parent table. Perhaps you're entering a parent and children records, but the parent record failed to be added. You'll have to stop in this case instead of ignoring the error and continuing with adding the children.

2- Your tables don't have referential integrity like ON DELETE CASCADE and you deleted a record from the parent table, then you're trying to update a record from the child table that has its foreign key referencing the delete record from the parent table.

In this case, you may want to delete the children instead of updating them. Consider adding referential integrity to your tables.

Racil Hilan
  • 24,690
  • 13
  • 50
  • 55