3 added 88 characters in body
source | link

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.

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);

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.

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.

2 added 20 characters in body
source | link

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 DATASample 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);

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

There wherewere multiple reason whyreasons that your original query didn't work. Look at the code above to find you were specificwhere my code differs from yours.  

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);

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

There where multiple reason why your query didn't work. Look at the code to find you were specific my code differs from yours.  

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);

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.

1
source | link

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);

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

There where multiple reason why your query didn't work. Look at the code to find you were specific my code differs from yours.