SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';


-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `users` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `emailadress` VARCHAR(80) NOT NULL ,
  `password` VARCHAR(32) NOT NULL ,
  `forename` VARCHAR(30) NOT NULL ,
  `surname` VARCHAR(30) NOT NULL ,
  `birthday` DATE NULL ,
  `asterisk` TINYINT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SHOW WARNINGS;
CREATE UNIQUE INDEX `unique_users_keys` ON `users` (`emailadress` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `user_eye_colours`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `user_eye_colours` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `user_eye_colours` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(15) NOT NULL ,
  `created` DATETIME NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SHOW WARNINGS;
CREATE UNIQUE INDEX `unique_eye_colours_keys` ON `user_eye_colours` (`name` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `user_informations`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `user_informations` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `user_informations` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `size` VARCHAR(5) NULL ,
  `eye_color` TINYINT NULL ,
  `hobbies` VARCHAR(255) NULL ,
  `dream_job` VARCHAR(80) NULL ,
  `favourite_colours` VARCHAR(80) NULL ,
  `favourite_animals` VARCHAR(80) NULL ,
  `favourite_eat` VARCHAR(80) NULL ,
  `favourite_school_subjects` VARCHAR(80) NULL ,
  `favourite_teachers` VARCHAR(80) NULL ,
  `fan_from` VARCHAR(255) NULL ,
  `i_hate` TEXT NULL ,
  `i_wish_you` TEXT NULL ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `fk_user_id`
    FOREIGN KEY (`user_id` )
    REFERENCES `digiposi`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_eye_colours`
    FOREIGN KEY (`eye_color` )
    REFERENCES `digiposi`.`user_eye_colours` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;
CREATE UNIQUE INDEX `unique_informations_key` ON `user_informations` (`user_id` ASC) ;

SHOW WARNINGS;
CREATE INDEX `fk_user_id` ON `user_informations` (`user_id` ASC) ;

SHOW WARNINGS;
CREATE INDEX `fk_eye_colours` ON `user_informations` (`eye_color` ASC) ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `user_asterisks`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `user_asterisks` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `user_asterisks` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `created` DATETIME NOT NULL ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `fk_users`
    FOREIGN KEY (`id` )
    REFERENCES `digiposi`.`users` (`asterisk` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;
CREATE UNIQUE INDEX `unique_asterisks_keys` ON `user_asterisks` (`name` ASC) ;

SHOW WARNINGS;
CREATE INDEX `fk_users` ON `user_asterisks` (`id` ASC) ;

SHOW WARNINGS;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;