I have the following piece of a schema that includes nested table relationships where a child has relationships with both parents and grand-parents where it CASCADES on one side and RESTRICT on the other:
CREATE TABLE `r180_topic_cd` ( `topic_cd_id` mediumint(9) NOT NULL, `cd_name` varchar(32) NOT NULL default '', `topic_name` varchar(255) NOT NULL default '', `active` tinyint(1) NOT NULL default '0', `description` varchar(255) NOT NULL default '', PRIMARY KEY (`topic_cd_id`), UNIQUE KEY `r180_topic_cd_idx_01` (`topic_cd_id`), UNIQUE KEY `r180_topic_cd_idx_02` (`cd_name`) ) TYPE=InnoDB COMMENT='tpcname.dbf'; CREATE TABLE `r180_student` ( `student_id` mediumint(9) NOT NULL auto_increment, `user_id` varchar(32) NOT NULL default '', `topic_cd_id` mediumint(9) NOT NULL default '-1', PRIMARY KEY (`student_id`), KEY `r180_student_idx_02` (`topic_cd_id`), UNIQUE KEY `r180_student_idx_04` (`user_id`), CONSTRAINT `0_3834` FOREIGN KEY (`topic_cd_id`) REFERENCES `r180_topic_cd` (`topic_cd_id`) ON DELETE RESTRICT, CONSTRAINT `0_3836` FOREIGN KEY (`user_id`) REFERENCES `ic_user` (`user_id`) ON DELETE CASCADE ) TYPE=InnoDB COMMENT='parts of student.dbf'; Parent of r180_log and child of r180_student: CREATE TABLE `r180_assignment` ( `r180_assignment_id` mediumint(9) NOT NULL auto_increment, `student_id` mediumint(9) NOT NULL default '0', `topic_cd_id` mediumint(9) NOT NULL default '0', PRIMARY KEY (`r180_assignment_id`), KEY `r180_assignment_idx_01` (`student_id`), KEY `r180_assignment_idx_02` (`topic_cd_id`), CONSTRAINT `0_3779` FOREIGN KEY (`student_id`) REFERENCES `r180_student` (`student_id`) ON DELETE CASCADE, CONSTRAINT `0_3780` FOREIGN KEY (`topic_cd_id`) REFERENCES `r180_topic_cd` (`topic_cd_id`) ON DELETE RESTRICT ) TYPE=InnoDB COMMENT='r180asgn.dbf'; Child of r180_assignment and r180_student, grand-child of r180_student and r180_topic_id: CREATE TABLE `r180_log` ( `log_id` mediumint(9) NOT NULL auto_increment, `student_id` mediumint(9) NOT NULL default '0', `assignment_id` mediumint(9) NOT NULL default '0', PRIMARY KEY (`log_id`), KEY `r180_log_idx_01` (`student_id`), KEY `r180_log_idx_02` (`assignment_id`), CONSTRAINT `0_3783` FOREIGN KEY (`student_id`) REFERENCES `r180_student` (`student_id`) ON DELETE RESTRICT, CONSTRAINT `0_6784` FOREIGN KEY (`assignment_id`) REFERENCES `r180_assignment` (`r180_assignment_id`) ON DELETE CASCADE ) TYPE=InnoDB COMMENT='r180log.dbf'; Is it true that due to this set of conflicting definitions a child record is destined for a delete from one side of its parental relationship and for restricting the delete from the other - RESULTING IN A RESTRICT operation? How does the algorithm operation work in this table schema? Thank you much in advance - Noga
