Tables are empty set in MySQL

Question

I used MySQL Workbench to generate a database and now I inserted it into the command-line client using:

mysql> . C:\Documents and Settings\kdegroote\My Documents\School\2008-2009\ICT2 \Gegevensbanken\Labo\Hoofdstuk 3 oef 6\pizzasecondtry.sql

For some reason, the last table won't be accepted. "Cannot create table" is the error message.

I manually editted the data to basically be the same, just without the special options Workbench adds to it and it worked like that.

I've been studying the original but I don't understand why it won't show me the tables. So I was wondering if anybody here could have a look at it. Maybe someone else will see what I'm overlooking.

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;

Answer 1

Try use PizzaDelivery before running show tables. You created your tables in the PizzaDelivery schema, but you probably connected with a different default schema. The default schema is the parameter to the mysql command line client:

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

(note: -p means prompt for password when it is not given an argument, which you generally shouldn't do because arguments show up in ps output and also get saved to disk in shell history. All of the command line parameters are optional.)

Answer 2

PROBLEM SOLVED.

I solved my problem here rather by accident. The problem appeared to be the constraints in the "Deliveries" table.

The 2 primairy keys are foreign keys at the same time and that generates and error if you want to constrain them.

So I simply left out the constraints and everything works.

Answer 3

You can often get more information from an InnoDB error like this:

mysql> SHOW ENGINE INNODB STATUS;

The output is long, but among 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.

The problem is that the Deliveries and the Orders tables both declare a foreign key constraint named employee_id.

Constraint names must be unique across all tables in a given database. The "errno: 121" is an InnoDB error code indicating a duplicate key error. In this case, the uniqueness of constraint names is not satisfied.

You can fix this problem and still keep your foreign key constraints if you just change the name of the declared constraint, for example:

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