Identity 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

-- An Orm Apart -- Monday 1st of February 2016 09:47:35 AM
--
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = 'ANSI';
-- If database does not exist, create the database
CREATE DATABASE IF NOT EXISTS Webshop;

Constraints FK uitzetten

-- 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;

LoginAttempt

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE LoginAttempt
-- Created on Monday 1st of February 2016 09:47:35 AM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `LoginAttempt`;
CREATE TABLE `LoginAttempt` (
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`MemberId` INT NOT NULL,
	`Time` VARCHAR (30) NOT NULL;

User

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE User
-- Created on Monday 1st of February 2016 09:47:35 AM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `User`;
CREATE TABLE `User` (
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`UserName` NVARCHAR (50) NOT NULL,
	`Email` VARCHAR (80) NOT NULL,
	`Password` CHAR (128) NOT NULL,
	`LastActivity` TIMESTAMP NULL,
	`FirstLogin` TIMESTAMP NULL,
	`Authenticated` BIT NULL,
	`LockedOut` BIT NULL,
	CONSTRAINT uc_User_UserName UNIQUE (UserName),
	CONSTRAINT uc_User_Email UNIQUE (Email));

Role

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Role
-- Created on Monday 1st of February 2016 09:47:35 AM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `Role`;
CREATE TABLE `Role` (
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`Name` NVARCHAR (255) NOT NULL,
	`Description` NVARCHAR (255) NULL,
	CONSTRAINT uc_Role_Name UNIQUE (Name));

UserRole

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE UserRole
-- Created on Monday 1st of February 2016 09:47:35 AM
-- 
USE `Webshop`;
DROP TABLE IF EXISTS `UserRole`;
CREATE TABLE `UserRole` (
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(Id),
	`MemberId` INT NOT NULL,
	`RoleId` INT NOT NULL,
	CONSTRAINT fk_UserRoleMemberId FOREIGN KEY (`MemberId`) REFERENCES `User` (`Id`),
	CONSTRAINT fk_UserRoleRoleId FOREIGN KEY (`RoleId`) REFERENCES `Role` (`Id`));

Foreign key constraints weer activeren

-- 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;