Grocery Crud - CodeIgniter relation_n_n error

Question

I have a problem while trying to save data (or update) in a crud with n_n relation, i have a product table, who is related to a product_detail, product detail has 3 foreign keys, to connect to a table called color, and other called material. Any idea why fail?, i appreciate your help, thanks.

The error is this:

A Database Error Occurred

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (medina_db.product_detail, CONSTRAINT product_detail_ibfk_3 FOREIGN KEY (material_id) REFERENCES material (id))

INSERT INTO product_detail (product_id, color_id) VALUES ('4', '1')

Filename: /Applications/MAMP/htdocs/industrias_medina/models/grocery_crud_model.php

Line Number: 413

---

My Grocery Crud code:

$crud = new grocery_CRUD();
$crud->set_table('product')->set_subject('Productos');
$crud->set_relation_n_n("Colores", 'product_detail', 'color', 'product_id', 'color_id', 'name');
$crud->set_relation_n_n("Materiales", 'product_detail', 'material', 'product_id', 'material_id', 'name');
$crud->set_field_upload('image','assets/uploads/files');
$crud->fields('name','model','description', "Colores", "Materiales", 'image');
$output = $crud->render();
$this->_productos_output($output);

---

SQL:

CREATE TABLE `color` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=ucs2 AUTO_INCREMENT=2 ;

CREATE TABLE `material` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier',
  `name` varchar(250) NOT NULL COMMENT 'Product''s name',
  `model` varchar(250) NOT NULL COMMENT 'Product''s model',
  `description` varchar(400) NOT NULL COMMENT 'Product''s description',
  `image` varchar(400) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Table to store products' AUTO_INCREMENT=13 ;

CREATE TABLE `product_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `color_id` int(11) NOT NULL,
  `material_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`product_id`,`color_id`,`material_id`),
  KEY `product_id` (`product_id`),
  KEY `color_id` (`color_id`),
  KEY `material_id` (`material_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

Answer 1

It looks like your product_detail table has a material_id column that references the material table. Your insert statement is not inserting anything into that material_id column. You need to either insert a value into that column (that exists in material), set a default value that references the key in material, or set a default of NULL for that column.

Thanks,

Andrew