I made use of MySQL Work bench to develop a database and today I placed it in to the command-line client using:

mysql> . C:Documents and SettingskdegrooteMy DocumentsSchool2008-2009ICT2 GegevensbankenLaboHoofdstuk 3 oef 6pizzasecondtry.sql

For whatever reason, the final table will not be recognized. "Cannot create table" may be the error message.

I by hand editted the information to essentially function as the same, just with no special options Work bench contributes to it also it labored like this.

I have been staring at the original however i do not understand why it will not show me the tables. So I'm wondering if anybody here could take a look in internet marketing. Maybe another person might find what I am looking over.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `PizzaDelivery` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `PizzaDelivery`;


CREATE TABLE IF NOT EXISTS `PizzaDelivery`.`Visitors` (
  `visitor_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `adres` VARCHAR(45) NOT NULL ,
  `telephone` MEDIUMBLOB NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`visitor_id`))ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `PizzaDelivery`.`Employees` (
  `employee_id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`employee_id`))ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `PizzaDelivery`.`Orders` (
  `order_id` INT NOT NULL AUTO_INCREMENT ,
  `pizza` VARCHAR(45) NOT NULL ,
  `extra` VARCHAR(45) NULL ,
  `kind` VARCHAR(45) NOT NULL ,
  `amount` VARCHAR(45) NOT NULL ,
  `visitor_id` INT NOT NULL ,
  `employee_id` INT NOT NULL ,
  `order_time` TIME NOT NULL ,
  PRIMARY KEY (`order_id`) ,
  INDEX `visitor_id` (`visitor_id` ASC) ,
  INDEX `employee_id` (`employee_id` ASC) ,
  CONSTRAINT `visitor_id`
    FOREIGN KEY (`visitor_id` )
    REFERENCES `PizzaDelivery`.`Visitors` (`visitor_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `employee_id`
    FOREIGN KEY (`employee_id` )
    REFERENCES `PizzaDelivery`.`Employees` (`employee_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `PizzaDelivery`.`Deliveries` (
  `employee_id` INT NOT NULL ,
  `order_id` INT NOT NULL ,
  `voertuig_id` INT NOT NULL ,
  `deliverytime` TIME NOT NULL ,
  PRIMARY KEY (`employee_id`, `order_id`) ,
  INDEX `employee_id` (`employee_id` ASC) ,
  INDEX `order_id` (`order_id` ASC) ,
  CONSTRAINT `employee_id`
    FOREIGN KEY (`employee_id` )
    REFERENCES `PizzaDelivery`.`Employees` (`employee_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `order_id`
    FOREIGN KEY (`order_id` )
    REFERENCES `PizzaDelivery`.`Orders` (`order_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)ENGINE=InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

You are able to frequently have more information from an InnoDB error such as this:

mysql> SHOW ENGINE INNODB STATUS;

The output is lengthy, but one of the status output I saw this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
081221 12:02:36 Error in foreign key constraint creation 
for table `pizzadelivery/deliveries`.
A foreign key constraint of name `pizzadelivery/employee_id`
already exists.

However , the Deliveries and also the Orders tables both declare an overseas key constraint named employee_id.

Constraint names should be unique across all tables inside a given database. The "errno: 121" is definitely an InnoDB error code showing a replica key error. Within this situation, the originality of constraint names isn't satisfied.

You are able to fix this issue but still keep the foreign key constraints should you just alter the title from the declared constraint, for instance:

CREATE TABLE IF NOT EXISTS `PizzaDelivery`.`Deliveries` (
  . . .
  CONSTRAINT `employee_id2`
    FOREIGN KEY (`employee_id` )
  . . .

Try use PizzaDelivery before you run show tables. You produced your tables within the PizzaDelivery schema, however, you most likely of a different default schema. The default schema may be the parameter towards the mysql command line client:

$ mysql -h <db-host> -u <username> -p <schema-name>

(note: -p means prompt for password when it's not given a disagreement, that you simply generally should not do because arguments appear in ps output as well as get saved to disk in spend history. All the command line parameters are optional.)