0

There is one table. Parent_id references same table id. How insert raw if parent_id = id? I don't know id(before insert) and could't insert if there is not parent_id. What the optimal way without getting last INCREMENT id and creating temporary table? 

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) unsigned NOT NULL,
  `someotherfield` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY `constr_catalog_guid_id` (`parent_id`) REFERENCES `test` (`id`)   
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is not work:

Insert into 'test' values (parent_id = test.id, "NOt matter");
Cœur
  • 37,241
  • 25
  • 195
  • 267
Stepchik
  • 275
  • 1
  • 3
  • 14
  • Why do you want to duplicate it, just use a pre-defined value for no parent record. – xdazz Jun 21 '12 at 05:40
  • `id` is autoincrement and `id` is also `primary key`. How are you able to duplicate them? Explain! – Imdad Jun 21 '12 at 05:54

2 Answers2

2
CREATE TRIGGER trigger_increment_id_for_another_field BEFORE INSERT ON test FOR EACH ROW
BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test');
   SET NEW.parent_id=next_id;
END

This trigger shall duplicate the parent_id value based on the auto_increment value for every insert of a row.

sel
  • 4,982
  • 1
  • 16
  • 22
0

You can temporarily disable the enforcement of the foreign key, such as this:

SET foreign_key_checks = 0;
INSERT INTO 'test' VALUES (NULL, NULL, "foo");
UPDATE 'test' SET parent_id = id WHERE parent_id IS NULL;
SET foreign_key_checks = 1;

I'm not sure, but you may need to make the column allow NULL for this to work.

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

Chris Trahey
  • 18,202
  • 1
  • 42
  • 55