Mysql – creating table syntax error 1064

MySQLmysql-workbench

I'm using workbench to create a table. This script is giving me a problem. It is saying that there is a syntax error new '). 

DROP TABLE IF EXISTS `waitronmain`.`managers` ;

CREATE  TABLE IF NOT EXISTS `waitronmain`.`managers` (
  `manager_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `restaurant` VARCHAR(45) NOT NULL ,
  `location` VARCHAR(45) NULL ,
  PRIMARY KEY (`manager_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `waitronmain`.`waiters`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `waitronmain`.`waiters` ;

CREATE  TABLE IF NOT EXISTS `waitronmain`.`waiters` (
  `waiter_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`waiter_id`) ,
  INDEX `manager_id` () ,
  CONSTRAINT `manager_id`
    FOREIGN KEY ()
    REFERENCES `waitronmain`.`managers` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The line causing the problem is this:

INDEX `manager_id` () ,

Any ideas? I'm at a loss, the code was generated from an ERD model I made within Workbench so I'm suprised it has a syntax error at all. I got this error code when executing:

Error Code: 1064 You have an error in your SQL syntax

Best Answer

I see a few issues with your existing code to create the waiters table.

First, the index creation code is incorrect. The column to be indexed should be inside the parentheses -- INDEX (manager_id).

Second, you are trying to add an index on a column that is not listed in your waiters table. You need to include the manager_id as a column.

Third, the syntax for the FOREIGN KEY constraint is incorrect. The syntax is from the MySQL docs is:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

So your code should be:

CREATE  TABLE `waiters` (
  `waiter_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `manager_id` INT NOT NULL,
  PRIMARY KEY (`waiter_id`) ,
  INDEX  (`manager_id`) ,
  CONSTRAINT FK_Manager
    FOREIGN KEY (`manager_id`)
    REFERENCES `managers` (manager_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

See SQL Fiddle with Demo

Related Question