mmt - 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.

We zijn begonnen met tabelnamen en kolomnamen in camelcase notatie te schrijven. We beslissen nu om toch maar bij pascalnotatie te blijven.

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 database of de tabel.

Oplossing

Globals instellen (alleen op je lokale MySQL Server en Cloud9, niet op de remote MySQL server van mij)

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = 'ANSI';

Database maken (alleen op je lokale MySQL Server en Cloud9, niet op de remote MySQL server van mij) en activeren

-- If database does not exist, create the database
CREATE DATABASE IF NOT EXISTS mmt;
USE mmt;

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;

Maak de tabellen

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Curiosity
-- Created on Sunday 27th of January 2019 09:09:52 AM
-- 
DROP TABLE IF EXISTS `Curiosity`;
CREATE TABLE `Curiosity` (
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`Name` NVARCHAR (255) NOT NULL,
	`Image` VARCHAR (255) NULL,
	`Comment` NVARCHAR (255) NULL,
	`Type` NVARCHAR (255) NULL,
	`Period` NVARCHAR (255) NULL,
	`Coordinates` VARCHAR (80) NULL,
	`Longitude` VARCHAR (25) NULL,
	`Latitude` VARCHAR (25) NULL,
	`Country` NVARCHAR (120) NULL,
	`Region` NVARCHAR (120) NULL,
	`Province` NVARCHAR (255) NULL,
	`City` NVARCHAR (255) NULL);

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Log
-- Created on Sunday 27th of January 2019 09:09:52 AM
-- 
DROP TABLE IF EXISTS `Log`;
CREATE TABLE `Log` (
	`UserName` NVARCHAR (50) NOT NULL,
	`Email` NVARCHAR (255) NOT NULL,
	`Role` NVARCHAR (50) NULL,
	`Longitude` CHAR (25) NOT NULL,
	`Latitude` CHAR (25) NOT NULL,
	`CuriosityName` NVARCHAR (255) NOT NULL,
	`CuriosityId` INT NOT NULL,
	`CuriosityLongitude` CHAR (255) NOT NULL,
	`CuriosityLatitude` CHAR (255) NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`InsertedOn` TIMESTAMP NOT NULL);

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Role
-- Created on Sunday 27th of January 2019 09:09:52 AM
-- 
DROP TABLE IF EXISTS `Role`;
CREATE TABLE `Role` (
	`Name` NVARCHAR (50) NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_Role_Name UNIQUE (Name));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE User
-- Created on Sunday 27th of January 2019 09:09:52 AM
-- 
DROP TABLE IF EXISTS `User`;
CREATE TABLE `User` (
	`Name` NVARCHAR (50) NOT NULL,
	`Salt` NVARCHAR (255) NULL,
	`HashedPassword` NVARCHAR (255) NULL,
	`RoleId` INT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT uc_User_Name UNIQUE (Name),
	CONSTRAINT fk_UserRoleId FOREIGN KEY (`RoleId`) REFERENCES `Role` (`Id`));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE CuriosityComment
-- Created on Sunday 27th of January 2019 09:09:52 AM
-- 
DROP TABLE IF EXISTS `CuriosityComment`;
CREATE TABLE `CuriosityComment` (
	`CuriosityId` INT NULL,
	`UserName` NVARCHAR (255) NOT NULL,
	`Comment` NVARCHAR (255) NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	CONSTRAINT fk_CuriosityCommentCuriosityId FOREIGN KEY (`CuriosityId`) REFERENCES `Curiosity` (`Id`));

Foreign Key constraints weer 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;