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.

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;
share|improve this question

migrated from serverfault.com Jan 17 '13 at 1:28

This question came from our site for professional system and network administrators.

add comment

2 Answers 2

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.

share|improve this answer
add comment

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.

share|improve this answer
2  
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. –  ypercube Jan 20 '13 at 11:00
    
@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. –  Mr. Radical Jan 20 '13 at 12:54
    
No need for transactions, no need for foreign key constraints -> no need for integrity then. –  ypercube Jan 20 '13 at 18:43
    
@ypercube his transactions are no update no delete on action. With regard to the data integrity you can accomplish that without transactions. –  Mr. Radical Jan 20 '13 at 18:45
add comment

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.