creating table syntax error 1064

Question

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

Answer 1

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