You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
73 lines
2.4 KiB
SQL
73 lines
2.4 KiB
SQL
-- MySQL Script generated by MySQL Workbench
|
|
-- Mon 05 Feb 2018 11:09:37 PM MST
|
|
-- Model: New Model Version: 1.0
|
|
-- MySQL Workbench Forward Engineering
|
|
|
|
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,ALLOW_INVALID_DATES';
|
|
|
|
-- -----------------------------------------------------
|
|
-- Schema openquestion
|
|
-- -----------------------------------------------------
|
|
|
|
-- -----------------------------------------------------
|
|
-- Schema openquestion
|
|
-- -----------------------------------------------------
|
|
CREATE SCHEMA IF NOT EXISTS `openquestion` DEFAULT CHARACTER SET utf8 ;
|
|
USE `openquestion` ;
|
|
|
|
-- -----------------------------------------------------
|
|
-- Table `openquestion`.`questions`
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `openquestion`.`questions` (
|
|
`qid` INT NOT NULL AUTO_INCREMENT,
|
|
`qtext` TEXT NOT NULL,
|
|
`qcode` VARCHAR(20) NOT NULL,
|
|
PRIMARY KEY (`qid`),
|
|
UNIQUE INDEX `qid_UNIQUE` (`qid` ASC))
|
|
ENGINE = InnoDB;
|
|
|
|
|
|
-- -----------------------------------------------------
|
|
-- Table `openquestion`.`answers`
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `openquestion`.`answers` (
|
|
`aid` INT NOT NULL AUTO_INCREMENT,
|
|
`atext` TEXT NOT NULL,
|
|
`qid` INT NOT NULL,
|
|
PRIMARY KEY (`aid`, `qid`),
|
|
UNIQUE INDEX `aid_UNIQUE` (`aid` ASC),
|
|
INDEX `fk_answers_questions_idx` (`qid` ASC),
|
|
CONSTRAINT `fk_answers_questions`
|
|
FOREIGN KEY (`qid`)
|
|
REFERENCES `openquestion`.`questions` (`qid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION)
|
|
ENGINE = InnoDB;
|
|
|
|
|
|
-- -----------------------------------------------------
|
|
-- Table `openquestion`.`responses`
|
|
-- -----------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `openquestion`.`responses` (
|
|
`rid` INT NOT NULL AUTO_INCREMENT,
|
|
`aid` INT NOT NULL,
|
|
`qid` INT NOT NULL,
|
|
`timestamp` DATETIME NOT NULL,
|
|
`name` VARCHAR(100) NULL,
|
|
PRIMARY KEY (`rid`, `aid`, `qid`),
|
|
UNIQUE INDEX `rid_UNIQUE` (`rid` ASC),
|
|
INDEX `fk_responses_answers1_idx` (`aid` ASC, `qid` ASC),
|
|
CONSTRAINT `fk_responses_answers1`
|
|
FOREIGN KEY (`aid` , `qid`)
|
|
REFERENCES `openquestion`.`answers` (`aid` , `qid`)
|
|
ON DELETE NO ACTION
|
|
ON UPDATE NO ACTION)
|
|
ENGINE = InnoDB;
|
|
|
|
|
|
SET SQL_MODE=@OLD_SQL_MODE;
|
|
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
|
|
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
|