Define foreign keys and add providers table.

skillsquares.sql

-- -----------------------------------------------------
-- Table `skillsquares`.`providers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `skillsquares`.`providers` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(150) NULL DEFAULT NULL ,
  `url` VARCHAR(255) NULL DEFAULT NULL ,
  `description` TEXT NULL DEFAULT NULL ,
  `is_active` TINYINT(1) NULL DEFAULT '1' ,
  `created` DATETIME NULL DEFAULT NULL ,
  `modified` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `skillsquares`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `skillsquares`.`users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NULL DEFAULT NULL ,
  `email` VARCHAR(150) NULL DEFAULT NULL ,
  `password` VARCHAR(255) NULL DEFAULT NULL ,
  `activation_key` VARCHAR(255) NULL DEFAULT NULL ,
  `is_spam` TINYINT(1) NULL DEFAULT NULL ,
  `is_admin` TINYINT(1) NULL DEFAULT NULL ,
  `is_active` TINYINT(1) NULL DEFAULT NULL ,
  `created` DATETIME NULL DEFAULT NULL ,
  `modified` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `skillsquares`.`tests`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `skillsquares`.`tests` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(100) NULL DEFAULT NULL ,
  `provider_id` INT(11) NOT NULL ,
  `icon` VARCHAR(255) NULL DEFAULT NULL ,
  `take_test_url` VARCHAR(255) NULL DEFAULT NULL ,
  `url_slug` VARCHAR(255) NULL DEFAULT NULL ,
  `is_active` TINYINT(1) NULL DEFAULT NULL ,
  `created` DATETIME NULL DEFAULT NULL ,
  `modified` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `provider_id_idx` (`id` ASC) ,
  CONSTRAINT `provider_id`
    FOREIGN KEY (`id` )
    REFERENCES `skillsquares`.`providers` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `skillsquares`.`scores`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `skillsquares`.`scores` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_id` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `test_id` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `score` INT(11) NOT NULL DEFAULT '0' ,
  `modified` DATETIME NULL DEFAULT NULL ,
  `created` DATETIME NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `user_id_idx` (`user_id` ASC) ,
  INDEX `test_id_idx` (`test_id` ASC) ,
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id` )
    REFERENCES `skillsquares`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `test_id`
    FOREIGN KEY (`test_id` )
    REFERENCES `skillsquares`.`tests` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `skillsquares`.`skills`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `skillsquares`.`skills` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `skillsquares`.`skills_tests`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `skillsquares`.`skills_tests` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `skill_id` INT(11) UNSIGNED NOT NULL ,
  `test_id` INT(11) UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `test_id_idx` (`test_id` ASC) ,
  INDEX `skill_id_idx` (`skill_id` ASC) ,
  CONSTRAINT `test_id`
    FOREIGN KEY (`test_id` )
    REFERENCES `skillsquares`.`tests` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `skill_id`
    FOREIGN KEY (`skill_id` )
    REFERENCES `skillsquares`.`skills` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = utf8;