relation_n_n error

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.

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 ;

Well your idea of usage is nearly correct but a n_n relation goes in conjunction like

product_colors / product_material.. there it would suffice the needs. But when u adding all in product details what will happen in here - it will add product / colors as 1 combination

product / details as other combination of entries.. there wont be a combination of rows together .. so if u select 6 colors / 5 materials.. it wont adjust and make them in single row.. but make 11 entries into product details table.

and if you notice the error what is coming in is .. or why it is coming in is that u have a constraint - material_key / color_id as not null ... and when it is making and entry just for product color - it is not adding material id to it.. hence it is failing in here.

Rather then using the gc's insert mechanism .. you can override the insert / update by yourself and handle the entries / creation of product_details table manually where u pick 1st color from colors and 1st material from materials .. merge both and make a single entry in product details table.. that should solve your problem.

But again.. u have to make sure the user selects the same number of colors and materials.. and in the manner / order of combination the are requiring / expecting it to be .. else the process will fail in itself.

Happy GCing :)

Armando Gal-lo

Amit Shah