I have following table :
Code: Select all
CREATE TABLE `tbl_rel_hw_pos_gw` (
`hw_pos_gw_rel_id` INT(11) NOT NULL AUTO_INCREMENT,
`hw_pos_rel_id` INT(11) NOT NULL,
`gw_id` INT(11) DEFAULT NULL,
PRIMARY KEY (`hw_pos_gw_rel_id`),
UNIQUE KEY `UNQ_tbl_rel_hw_pos_gw` (`hw_pos_rel_id`,`gw_id`),
KEY `FK_tbl_rel_hw_pos_gw1` (`hw_pos_rel_id`),
KEY `FK_tbl_rel_hw_pos_gw2` (`gw_id`),
CONSTRAINT `CN_tbl_rel_hw_pos_gw1` FOREIGN KEY(`hw_pos_rel_id`) REFERENCES `tbl_rel_hw_pos`(`hw_pos_rel_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `CN_tbl_rel_hw_pos_gw2` FOREIGN KEY(`gw_id`) REFERENCES `tbl_guide_words`(`gw_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=INNODB ROW_FORMAT=COMPACT COMMENT='Relationship Between Headword - Part of Speech & Guideword';
Here, the unique key constraint fails when the row has following values :
Code: Select all
hw_pos_gw_rel_id hw_pos_rel_id gw_id
1 1 NULL
2 1 NULLBut, unique key constraint works when the table has following values :
Code: Select all
hw_pos_gw_rel_id hw_pos_rel_id gw_id
1 1 5
2 1 5Any idea ?
Please help.