I've checked it out for 4 times and didn't find any syntax error.
Asking for help here and thx!!!!
and my MySQL version is 5.5
error message is:1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'ADD CONSTRAINT fk_notification_source_id FOREIGN KEY
(source_id) REFERENCES ' at line 1CREATE TABLE IF NOT EXISTS `object_type` (
`type_id` tinyint(1) NOT NULL,
`type_name` varchar(45) NOT NULL,
`desc` varchar(45) NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `op_type` (
`type_id` tinyint(1) NOT NULL,
`type_name` varchar(45) NOT NULL,
`desc` varchar(45) NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE IF NOT EXISTS `user_op` (
`op_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`op_type` tinyint(1) NOT NULL,
`op_data` varchar(45) NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`op_id`, `user_id`),
KEY `fk_op_user_id_idx` (`user_id`),
KEY `fk_op_op_type_idx` (`op_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `notification_type` (
`type_id` int(11) NOT NULL,
`type_name` varchar(45) NOT NULL,
`desc` varchar(45) NOT NULL,
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `user_notification` (
`notif_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`notif_type` int(11) NOT NULL,
`source_id` int(11) NOT NULL,
`path` varchar(45) NOT NULL,
`parent_id` int(11) NOT NULL,
`parent_type` tinyint(1) NOT NULL,
`count` int(11) NOT NULL,
`time` datetime NOT NULL,
`lasttimeread` datetime NOT NULL,
PRIMARY KEY (`notif_id`, `user_id`),
KEY `fk_notification_user_id_idx` (`user_id`),
KEY `fk_notification_type_id_idx` (`notif_type`),
KEY `fk_notification_source_id_idx` (`source_id`),
KEY `fk_notification_parent_type_idx` (`parent_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `user_op`
ADD CONSTRAINT `fk_op_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_op_op_type` FOREIGN KEY (`op_type`) REFERENCES `op_type` (`type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE `user_notification`
ADD CONSTRAINT `fk_notification_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_notification_type_id` FOREIGN KEY (`notif_type`) REFERENCES `notification_type` (`type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_notification_source_id` FOREIGN KEY (`source_id`) REFERENCES `user_op` (`op_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_notification_parent_type` FOREIGN KEY (`parent_type`) REFERENCES `object_type` (`type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
thank you!!
p.s my user table is as below:CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`department_id` int(11) NOT NULL,
`school_id` int(11) NOT NULL,
`password` varchar(45) NOT NULL,
`realname` varchar(45) NOT NULL,
`familyname` varchar(45) NOT NULL,
`birthdate` varchar(45) NOT NULL,
... ---some other properties.
PRIMARY KEY (`user_id`),
KEY `fk_user_department_id_idx` (`department_id`),
KEY `fk_user_school_id_idx` (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;