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.

• ypercubeᵀᴹ: How can you use foreign keys without InnoDB? MyISAM and Memory engines do not support foreign keys. Only InnoDB and NDB support foreign keys and transactions.
• Mr. Radical: @ypercube in most cases there is no need for transactions. Why over complicate matters if you can just reference a key in another table by using joins.
• ypercubeᵀᴹ: No need for transactions, no need for foreign key constraints -> no need for integrity then.
• Mr. Radical: @ypercube his transactions are no update no delete on action. With regard to the data integrity you can accomplish that without transactions.