Can't create table with this script

Question

Trying to create a table with the script below. Every time I execute it I get the following error and can't work out why?!

#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 cat_id FOREIGN KEY () REFERENCES waitron.`catego' at line 7 **

CREATE  TABLE IF NOT EXISTS `waitron`.`dishes` (
      `dish_id` INT NOT NULL AUTO_INCREMENT ,
      `dish_name` VARCHAR(90) NULL ,
      `dish_desc` VARCHAR(200) NULL ,
      `price` DECIMAL(19,4) NULL ,
      PRIMARY KEY (`dish_id`) ,
      INDEX `cat_id` () ,
      CONSTRAINT `cat_id`
        FOREIGN KEY ()
        REFERENCES `waitron`.`categories` ()
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;

Answer 1

The index you have named cat_id doesn't have any columns in it. You must actually index something in your index. The FOREIGN KEY () column list is also empty. You must actual point at some foreign keys.

Answer 2

Try this:

CREATE  TABLE IF NOT EXISTS waitron(
      `categories` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `name` VARCHAR (30)
 )ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS dishes (
      `dish_id` INT NOT NULL AUTO_INCREMENT ,
      `dish_name` VARCHAR(90) NULL ,
      `dish_desc` VARCHAR(200) NULL ,
      `price` DECIMAL(19,4) NULL ,
      `cat_id` INT,
      PRIMARY KEY (`dish_id`) ,
      INDEX (`cat_id`), 
        FOREIGN KEY (`cat_id`)
        REFERENCES `waitron` (`categories`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
)ENGINE = InnoDB;

Sample data:

INSERT INTO waitron(name)
VALUES
("seafood"),
("starters"),
("maincourse");

INSERT INTO dishes (dish_name, dish_desc, price, cat_id)
VALUES ("french fries", "lkjlj", 5.5000, 3);

http://www.sqlfiddle.com/#!2/28762/2

Do you really need to use the InnoDB engine? You can use foreign keys without using the InnoDB engine. Because you are using InnoDB I needed to create all the referencing tables at once.

There were multiple reasons that your original query didn't work. Look at the code above to find where my code differs from yours.