Mysql – Can’t create table with this script

MySQL

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;

Best Answer

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.

Related Solutions

Mysql – What’s wrong with this MySQL query

You are using the wrong quotes. Use ` instead of '

CREATE TABLE `topics` (
  `topicid` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `creator` varchar(255) NOT NULL,
  `createtime` datetime NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB;

I also would recommend specifying the engine.

Mysql query not working

Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!