mysql约束问题

rpppsulh  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(289)

获取最终形式的后视图定义。
没有东西要拿
在服务器中执行sql脚本将导致:

ERROR: Error 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 'CONSTRAINT `fk_employee_department`
FOREIGN KEY (`dpt_id`)
REFERENCES `r' at line 16

sql代码:

CREATE TABLE IF NOT EXISTS `rrm17b`.`employee` (
      `emp_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
      `dpt_id` INT UNSIGNED NULL,
      `emp_first` VARCHAR(20) NOT NULL,
      `emp_last` VARCHAR(20) NOT NULL,
      `emp_type` ENUM('f', 'p') NOT NULL,
      `emp_street` VARCHAR(30) NOT NULL,
      `emp_city` VARCHAR(20) NOT NULL,
      `emp_state` CHAR(2) NOT NULL,
      `emp_zip` INT UNSIGNED NOT NULL,
      `emp_phone` BIGINT UNSIGNED NOT NULL,
      `emp_email` VARCHAR(45) NOT NULL,
      `emp_notes` VARCHAR(100) NULL,
      PRIMARY KEY (`emp_id`),
      INDEX `fk_employee_department_idx` (`dpt_id` ASC) 
      CONSTRAINT `fk_employee_department`
        FOREIGN KEY (`dpt_id`)
        REFERENCES `rrm17b`.`department` (`dpt_id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

sql脚本执行完成:语句:9成功,1失败
获取最终形式的后视图定义。没有东西要拿

anauzrmj

anauzrmj1#

请试试这个:

CREATE TABLE IF NOT EXISTS rrm17b.employee(
emp_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
dpt_id INT UNSIGNED NULL,
emp_first VARCHAR(20) NOT NULL,
emp_last VARCHAR(20) NOT NULL,
emp_type ENUM('f', 'p') NOT NULL,
emp_street VARCHAR(30) NOT NULL,
emp_city VARCHAR(20) NOT NULL,
emp_state CHAR(2) NOT NULL,
emp_zip INT UNSIGNED NOT NULL,
emp_phone BIGINT UNSIGNED NOT NULL,
emp_email VARCHAR(45) NOT NULL,
emp_notes VARCHAR(100) NULL,
      PRIMARY KEY (emp_id),
      INDEX fk_employee_department_idx(dpt_id ASC),
      CONSTRAINT fk_employee_department
      FOREIGN KEY (dpt_id)
      REFERENCES rrm17b.department(dpt_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB

修复了多个问题:
--在索引后添加逗号
--删除dpt\u id后的“`”

相关问题