MySQL #1064 ALTER添加多个外键约束

 手机用户2502852637_666 发布于 2022-11-06 08:29

see here also: http://stackoverflow.com/questions/13...

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 1
CREATE 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 ;
撰写答案
今天,你开发时遇到什么问题呢?
立即提问
热门标签
PHP1.CN | 中国最专业的PHP中文社区 | PNG素材下载 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有