Mikmak MySQL Data Definition Language

Probleem

We maken de database en de tabellen niet aan met behulp van een visuele designer maar met behulp van SQL scripts.

Design

Vooraleer de database of een tabel te maken gaan we na als die al bestaat. Indien dit het geval is, deleten we eerst de tabel.

Oplossing

Database maken

drop database  if exists Webshop;
create database Webshop;

Foreign Key constraints opheffen

-- With the MySQL FOREIGN_KEY_CHECKS variable,
-- you don't have to worry about the order of your
-- DROP and CREATE TABLE statements at all, and you can
-- write them in any order you like, even the exact opposite.
SET FOREIGN_KEY_CHECKS = 0;

Country

Over de tabel Country valt niet veel te zeggen. Let erop hoe we een Unique constraint toevoegen voor de kolom Name en Code. Vooraleer de tabel te creëren gaan we na of de tabel al bestaat. Als de tabel bestaat wordt die eerst verwijderd.

-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Country
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `Country`;
CREATE TABLE `Country` (
	`Code` NVARCHAR (2) NOT NULL,
	`Latitude` FLOAT NULL,
	`Longitude` FLOAT NULL,
	`Name` NVARCHAR (255) NOT NULL,
	`ShippingCostMultiplier` FLOAT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_Country_Code UNIQUE (Code),
	CONSTRAINT uc_Country_Name UNIQUE (Name));

OrderStatus

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE OrderStatus
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `OrderStatus`;
CREATE TABLE `OrderStatus` (
	`Name` NVARCHAR (255) NOT NULL,
	`Description` NVARCHAR (1024) NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_OrderStatus_Name UNIQUE (Name));

Category

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Category
-- Created on Monday 11th of January 2016 01:42:48 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `Category`;
CREATE TABLE `Category` (
	`Name` NVARCHAR (255) NOT NULL,
	`Description` NVARCHAR (1024) NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_Category_Name UNIQUE (Name));

UnitBase

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE UnitBase
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `UnitBase`;
CREATE TABLE `UnitBase` (
	`Name` NVARCHAR (255) NOT NULL,
	`Description` NVARCHAR (1024) NULL,
	`ShippingCostMultiplier` FLOAT NULL,
	`Code` NVARCHAR (2) NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_UnitBase_Name UNIQUE (Name),
	CONSTRAINT uc_UnitBase_Code UNIQUE (Code));

ShippingMethod

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE ShippingMethod
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `ShippingMethod`;
CREATE TABLE `ShippingMethod` (
	`Name` NVARCHAR (255) NOT NULL,
	`Description` NVARCHAR (1024) NULL,
	`Price` DECIMAL(6,2) NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_ShippingMethod_Name UNIQUE (Name));

Supplier

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Supplier
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `Supplier`;
CREATE TABLE `Supplier` (
	`Code` NVARCHAR (10) NOT NULL,
	`Name` NVARCHAR (255) NOT NULL,
	`Contact` NVARCHAR (255) NULL,
	`Address` NVARCHAR (255) NULL,
	`City` NVARCHAR (255) NULL,
	`Region` NVARCHAR (80) NULL,
	`PostalCode` VARCHAR (20) NULL,
	`IdCountry` INT NOT NULL,
	`Phone` VARCHAR (40) NULL,
	`Mobile` VARCHAR (40) NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_Supplier_Code UNIQUE (Code),
	CONSTRAINT uc_Supplier_Name UNIQUE (Name),
	CONSTRAINT fk_SupplierIdCountry FOREIGN KEY (`IdCountry`) 
           REFERENCES `Country` (`Id`));

Curstomer

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Customer
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `Customer`;
CREATE TABLE `Customer` (
	`NickName` NVARCHAR (10) NOT NULL,
	`FirstName` NVARCHAR (255) NOT NULL,
	`LastName` NVARCHAR (255) NOT NULL,
	`Address1` NVARCHAR (255) NOT NULL,
	`Address2` NVARCHAR (255) NULL,
	`City` NVARCHAR (255) NOT NULL,
	`Region` NVARCHAR (80) NULL,
	`PostalCode` VARCHAR (20) NOT NULL,
	`IdCountry` INT NOT NULL,
	`Phone` VARCHAR (40) NULL,
	`Mobile` VARCHAR (40) NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT fk_CustomerIdCountry 
       FOREIGN KEY (`IdCountry`) REFERENCES `Country` (`Id`));

Product

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Product
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `Product`;
CREATE TABLE `Product` (
	`Description` NVARCHAR (1024) NULL,
	`Name` NVARCHAR (255) NOT NULL,
	`Price` FLOAT NULL,
	`ShippingCost` FLOAT NULL,
	`TotalRating` INT NULL,
	`Thumbnail` VARCHAR (255) NULL,
	`Image` VARCHAR (255) NULL,
	`DiscountPercentage` FLOAT NULL,
	`Votes` INT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`IdSupplier` INT NOT NULL,
	CONSTRAINT uc_Product_Name UNIQUE (Name),
	CONSTRAINT fk_ProductIdSupplier FOREIGN KEY (`IdSupplier`) 
          REFERENCES `Supplier` (`Id`));

Order

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Order
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `Order`;
CREATE TABLE `Order` (
	`OrderDate` DATETIME NOT NULL,
	`ShippingDate` DATETIME NOT NULL,
	`Comment` NVARCHAR (512) NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`IdCustomer` INT NOT NULL,
	`IdShippingMethod` INT NOT NULL,
	`IdStatus` INT NOT NULL,
	CONSTRAINT fk_OrderIdCustomer FOREIGN KEY (`IdCustomer`) 
         REFERENCES `Customer` (`Id`),
	CONSTRAINT fk_OrderIdShippingMethod 
         FOREIGN KEY (`IdShippingMethod`) REFERENCES `ShippingMethod` (`Id`),
	CONSTRAINT fk_OrderIdStatus FOREIGN KEY (`IdStatus`) 
         REFERENCES `OrderStatus` (`Id`));

OrderItem

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE OrderItem
-- Created on Wednesday 6th of January 2016 07:19:31 PM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `OrderItem`;
CREATE TABLE `OrderItem` (
	`IdProduct` INT NOT NULL,
	`IdOrder` INT NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`Quantity` DECIMAL(4,2) NULL,
	CONSTRAINT fk_OrderItemIdProduct 
        FOREIGN KEY (`IdProduct`) REFERENCES `Product` (`Id`),
	CONSTRAINT fk_OrderItemIdOrder 
        FOREIGN KEY (`IdOrder`) REFERENCES `Order` (`Id`));

Foreign Key constrains opnieuw instellen

-- With the MySQL FOREIGN_KEY_CHECKS variable,
-- you don't have to worry about the order of your
-- DROP and CREATE TABLE statements at all, and you can
-- write them in any order you like, even the exact opposite.
SET FOREIGN_KEY_CHECKS = 1;