001-order_demo-init.sql

CREATE DATABASE IF NOT EXISTS order_demo;
USE order_demo;

# Base table for products.
CREATE TABLE IF NOT EXISTS product (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL,
	name VARCHAR(256) NOT NULL,
	created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (id),
	UNIQUE (`name`)
)
ENGINE=InnoDB
;

# Base table for orders.
CREATE TABLE IF NOT EXISTS `order` (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL,
	created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (id)
)
ENGINE=InnoDB
;

# Base table for order items.
CREATE TABLE IF NOT EXISTS order_items (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL,
	order_id INT UNSIGNED NOT NULL,
	product_id INT UNSIGNED NOT NULL,
	created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (id),
  	CONSTRAINT `oi_order_FK` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  	CONSTRAINT `oi_product_FK` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE=InnoDB
;