Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.