Pizzas for winter school

pizzas.sql

//create database:
create database pizzaApi;


// create ingredients table:

CREATE TABLE `ingredients` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `key` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  UNIQUE INDEX `key_UNIQUE` (`key` ASC));


// create pizzas table:

CREATE TABLE `pizzas` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `key` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  UNIQUE INDEX `key_UNIQUE` (`key` ASC));


// create table pizzaIngredients:

CREATE TABLE `pizzaIngredients` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `pizzaId` INT NOT NULL,
  `ingredientId` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_pizzaDetails_1_idx` (`ingredientId` ASC),
  INDEX `fk_pizzaDetails_2_idx` (`pizzaId` ASC),
  CONSTRAINT `fk_pizzaDetails_1`
    FOREIGN KEY (`ingredientId`)
    REFERENCES `ingredients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pizzaDetails_2`
    FOREIGN KEY (`pizzaId`)
    REFERENCES `pizzas` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


// create table orders

CREATE TABLE `orders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `employeeId` INT NOT NULL,
  `contactNumber` VARCHAR(45) NOT NULL,
  `contactName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));


// create table orderDetails

CREATE TABLE `orderDetails` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `orderId` INT NOT NULL,
  `pizzaId` INT NOT NULL,
  `quantity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_orderDetails_1_idx` (`orderId` ASC),
  INDEX `fk_orderDetails_2_idx` (`pizzaId` ASC),
  CONSTRAINT `fk_orderDetails_1`
    FOREIGN KEY (`orderId`)
    REFERENCES `orders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_orderDetails_2`
    FOREIGN KEY (`pizzaId`)
    REFERENCES `pizzas` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);