Q:

mysql server unable to create complex constraint

I've got following 2 tables - tree_hierarchy:
tree_hierarchy | CREATE TABLE `tree_hierarchy` (
  `tree_id` int(10) unsigned NOT NULL,
  `time_segment` date NOT NULL,
  `parent_node_id` int(10) unsigned NOT NULL,
  `child_node_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`tree_id`,`time_segment`,`parent_node_id`,`child_node_id`),
  KEY `fk_tree_hierarchy_child_node_idx` (`child_node_id`,`time_segment`),
  KEY `fk_tree_hierarchy_parent_node_idx` (`parent_node_id`,`time_segment`),
  KEY `fk_tree_hierarchy_tree_id_idx` (`tree_id`),
  KEY `fk_tree_hierarchy_child_def` (`time_segment`),
  CONSTRAINT `fk_tree_hierarchy_tree_id` FOREIGN KEY (`tree_id`) REFERENCES `tree` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

and node_instance:
node_instance | CREATE TABLE `node_instance` (
  `id` int(10) unsigned NOT NULL,
  `node_id` int(10) unsigned NOT NULL,
  `time_segment` date NOT NULL,
  `name` varchar(128) NOT NULL,
  `sharing` enum('Private','Inherited','User','Open') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_node_ts` (`time_segment`,`node_id`),
  KEY `fk_node_instance_node_1` (`node_id`),
  KEY `fk_node_instance_time_segment_idx` (`time_segment`),
  CONSTRAINT `fk_node_instance_node_id` FOREIGN KEY (`node_id`) REFERENCES `node` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_node_instance_time_segment` FOREIGN KEY (`time_segment`) REFERENCES `time_segment` (`segment`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

When trying to put the constraint:
ALTER TABLE  `tree_hierarchy` ADD CONSTRAINT `fk_tree_hierarchy_child_xyz`
    FOREIGN KEY (`child_node_id` , `time_segment` )
    REFERENCES `node_instance` (`node_id` , `time_segment` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

I get:
ERROR 1005 (HY000): Can't create table 'empty_structure.#sql-4e8_28b1' (errno: 150)

Which means that my structure is not ok. But I think that both fields (node_id and time_segment are the same in both tables). When I create simple constraints, everything is ok:
ALTER TABLE  `tree_hierarchy` ADD CONSTRAINT `fk_tree_hierarchy_child_abcd`
    FOREIGN KEY (`time_segment` )
    REFERENCES `node_instance` (`time_segment` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

and
ALTER TABLE  `tree_hierarchy` ADD CONSTRAINT `fk_tree_hierarchy_child_efgh`
    FOREIGN KEY (`child_node_id` )
    REFERENCES `node_instance` (`node_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

I made sure that foreign key names are different (notice xyz, abcd, efgh suffix).
What's wrong with the former ADD CONSTRAINT?

A:

After some time I found that in node_instance I'm missing the appropriate complex key (node_id, time_segment). Instead, I have (time_segment,node_id) - and that's why mysql fails. After adding missing key, everything goes ok.

