0

i want to insert data to a table that contain foreign key, but it always gives me an error like this:

[2017-01-18 04:45:51] main.ERROR: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (goku.lime_courierservices, CONSTRAINT FK_EDC3A7832725FC587764FA1BFF56B114 FOREIGN KEY (couriers_id) REFERENCES lime_courier/lime_couriers (couriers_id) ON DELETE CASCADE)

Next Zend_Db_Statement_Exception: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (goku.lime_courierservices, CONSTRAINT FK_EDC3A7832725FC587764FA1BFF56B114 FOREIGN KEY (couriers_id) REFERENCES lime_courier/lime_couriers (couriers_id) ON DELETE CASCADE), query was: INSERT INTO lime_courierservices (courierservices_id, name, description, couriers_id) VALUES (?, ?, ?, ?)

i think the problem is the data that i insert is (?,?,?,?), but i already make sure by printing $model->getData() after $model->setData(), and the data was there, here's how i insert the data from model:

public function setCourierservices()
  {
    $model = $this->_courierserviceModel->create();
    $courierservices = $this->getCourierservices();
    foreach($courierservices as $courierservice){
      $data = array(
        'courierservices_id' => $courierservice->id,
        'name' => $courierservice->name,
        'description' => $courierservice->description,
        'couriers_id' => $courierservice->courier_id
      );
      $model->setData($data);
      try {
        $model->save();
      } catch (LocalizedException $e) {
        $this->_logger->addError($e->getMessage());
      } catch (\Exception $e) {
        $this->_logger->addError($e);
      }
    }
  }

here's how i create the table:

       $table_lime_courierservices->addColumn(
                'courierservices_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                array('identity' => false,'nullable' => false,'primary' => true,'unsigned' => true,),
                'Entity ID'
            );
            $table_lime_courierservices->addColumn(
                'name',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                null,
                [],
                'name'
            );
            $table_lime_courierservices->addColumn(
                'description',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                null,
                [],
                'description'
            );
            $table_lime_courierservices->addColumn(
                'couriers_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                15,
                ['nullable' => false],
                'couriers_id'
            );
         $table_lime_courierservices->addForeignKey(
             $installer->getFkName('lime_courier/lime_courierservices', 'couriers_id', 'lime_courier/lime_couriers','couriers_id'),
             'couriers_id',
             $installer->getTable('lime_courier/lime_couriers'),
             'couriers_id',
             \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
        );
  • Use direct SQL query instead of Model to insert data if you are facing foreign key issue. – BornCoder Jan 18 '17 at 5:15
  • @BornCoder i'm avoiding using direct SQL query, because the problem is exist with other model that contain foreign key too – Idham Choudry Jan 18 '17 at 5:29
  • then setId of foreign key when you are saving and first getData and check that your are getting foreign key in that or not – BornCoder Jan 18 '17 at 5:29
  • @BornCoder the data is correct when i'm doing get data – Idham Choudry Jan 18 '17 at 5:42
  • 1
    @BornCoder it turns out my foreign key is pointing at wrong table, because when i install table to add foreign key like this $installer->getTable('lime_courier/lime_couriers'), the solution is to go like this $installer->getTable('lime_couriers'), – Idham Choudry Jan 18 '17 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.