CODEWITHSUNDEEP
mysql
UmiBlah
UmiBlah

Reputation: 1

MySql CONSTRAINT issues

Fetching back view definitions in final form.

Nothing to fetch

Executing SQL script in server results in:

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 Code:

    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 script execution finished: statements: 9 succeeded, 1 failed

Fetching back view definitions in final form. Nothing to fetch

Upvotes: 0

Views: 92

Answers (1)

Muhammad Bilal
Muhammad Bilal

Reputation: 2124

Please try this:

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

Fixed multiple things:

--Added comma after INDEX

--Removed "`" after dpt_id

Upvotes: 1

Related Questions