Inserting Record into Database with CakePHP Results in Foreign Key Violation

Question

I'm now to CakePHP (a few days) and I'm having issues with inserting records with Foreign Keys.

Basic idea, Warehouses.

I have a warehouses table that holds all the available warehouses and I have a warehouse_types table to hold the different types of warehouses. The warehouse_types table already has some data in it.

I have a simple add view (based off the blog example in CakePHP's Cookbook) and I have it displaying the names of the different types from the database.

When I save the page I get a Database error.

Error: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`kvs`.`warehouses`, CONSTRAINT `warehouses_ibfk_1` FOREIGN KEY (`warehouse_type_id`) REFERENCES `warehouse_types` (`id`))

The SQL that is being generated is:

SQL Query: INSERT INTO `kvs`.`warehouses` (`name`, `location`, `modified`, `created`) VALUES ('Name Text', 'Location Text', '2014-04-02 12:07:13', '2014-04-02 12:07:13')

If I output the information in the request->data object, it shows that it is getting a value for the warehouseType_id.

array(
    'Warehouse' => array(
        'name' => 'Name Text',
        'location' => 'Location Text',
        'warehouseType_id' => '1'
    )
)

Any help on this would be awesome!

My database tables are as follows:

CREATE TABLE `warehouses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `warehouse_type_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `warehouse_type_id` (`warehouse_type_id`),
  CONSTRAINT `warehouses_ibfk_1` FOREIGN KEY (`warehouse_type_id`) REFERENCES `warehouse_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

CREATE TABLE `warehouse_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

My models are as follows:

class Warehouse extends AppModel{
    public $belongsTo = 'WarehouseType';
}

class WarehouseType extends AppModel{

}

My view (add.ctp) is as follows:

<h1>Add Warehouse</h1>
<?php
echo $this->Form->create('Warehouse');
echo $this->Form->input('name');
echo $this->Form->input('location');
echo $this->Form->input('warehouseType_id');
echo $this->Form->end('Create Warehouse');
?>

Finally, the add method in my WarehousesController:

public function add(){
        //Check Request to see if it is a Post
        if($this->request->is('post')){
            $this->Warehouse->create(); //Make a new Warehouse object
            debug($this->request->data);
            if($this->Warehouse->save($this->request->data)){ //Try and save Warehouse
                $this->Session->setFlash(__('Warehouse '.$this->request->data['Warehouse']['name'].' created'));
                return $this->redirect(array('action' => 'index')); //Redirect back to Warehouse list
            }
            $this->Session->setFlash(__('Unable to add Warehouse :('));
        }
        $this->set('warehouseTypes', $this->Warehouse->WarehouseType->find('list'));
    }

I've read over this answer here that seems the most relevant, but I'm lost: CakePHP Can't insert record with foreign key error

Also reviewed their documentation on saving: http://book.cakephp.org/2.0/en/models/saving-your-data.html#saving-related-model-data-hasone-hasmany-belongsto

AgRizzo AgRizzo · Accepted Answer · 2014-04-05T19:17:18

Your column name is different than what is being used in CakePHP. Change CakePHP to use warehouse_type_id. (Your CakePHP code is using warehouseType_id)

Inserting Record into Database with CakePHP Results in Foreign Key Violation

2 Answers

Add Warehouse