detectionDB_v1.sql

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
CREATE SCHEMA IF NOT EXISTS `detectionDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_turkish_ci ;
USE `detectionDB` ;
CREATE TABLE IF NOT EXISTS `detectionDB`.`User` (`userID` INT UNSIGNED NOT NULL AUTO_INCREMENT, `email` VARCHAR(64) NOT NULL, password VARCHAR(64) NOT NULL,`name` VARCHAR(45) NULL,`surname` VARCHAR(45) NULL, `phone` VARCHAR(45) NULL, PRIMARY KEY (`userID`), UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `detectionDB`.`Farm` (`farm_name` VARCHAR(45) NOT NULL, `categories` VARCHAR(45) NULL,`User_userID` INT UNSIGNED NOT NULL, PRIMARY KEY (`farm_name`, `User_userID`), INDEX `fk_Farm_User_idx` (`User_userID` ASC) VISIBLE, CONSTRAINT `fk_Farm_User` FOREIGN KEY (`User_userID`) REFERENCES `detectionDB`.`User` (`userID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `detectionDB`.`Report` ( `reportID` INT NOT NULL AUTO_INCREMENT,`date` TIMESTAMP(1) NULL,`elapsed_time` INT NOT NULL,`total_left` INT NULL,`total_right` INT NULL,`User_userID` INT UNSIGNED NOT NULL,PRIMARY KEY (`reportID`, `User_userID`),UNIQUE INDEX `date_UNIQUE` (`date` ASC) VISIBLE,INDEX `fk_Report_User1_idx` (`User_userID` ASC) VISIBLE,CONSTRAINT `fk_Report_User1`  FOREIGN KEY (`User_userID`)  REFERENCES `detectionDB`.`User` (`userID`)  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;