MYSQL 50
Foreign Key Index Issue By admin on 5th November 2021 10:12:42 PM
  1. -- MySQL Workbench Forward Engineering
  2.  
  3. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  4. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  5. 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';
  6.  
  7. -- -----------------------------------------------------
  8. -- Schema azziedev_attendtudb
  9. -- -----------------------------------------------------
  10.  
  11. -- -----------------------------------------------------
  12. -- Schema azziedev_attendtudb
  13. -- -----------------------------------------------------
  14. CREATE SCHEMA IF NOT EXISTS `azziedev_attendtudb` DEFAULT CHARACTER SET utf8 ;
  15. USE `azziedev_attendtudb` ;
  16.  
  17. -- -----------------------------------------------------
  18. -- Table `azziedev_attendtudb`.`Branch`
  19. -- -----------------------------------------------------
  20. CREATE TABLE IF NOT EXISTS `azziedev_attendtudb`.`Branch` (
  21.   `branchNo` VARCHAR(5) NOT NULL,
  22.   `street` VARCHAR(45) NOT NULL,
  23.   `city` VARCHAR(45) NOT NULL,
  24.   `postcode` VARCHAR(45) NOT NULL,
  25.   PRIMARY KEY (`branchNo`))
  26.  
  27.  
  28. -- -----------------------------------------------------
  29. -- Table `azziedev_attendtudb`.`Staff`
  30. -- -----------------------------------------------------
  31. CREATE TABLE IF NOT EXISTS `azziedev_attendtudb`.`Staff` (
  32.   `staffNo` VARCHAR(20) NOT NULL,
  33.   `fName` VARCHAR(45) NOT NULL,
  34.   `lName` VARCHAR(45) NOT NULL,
  35.   `position` VARCHAR(45) NOT NULL,
  36.   `sex` VARCHAR(1) NOT NULL,
  37.   `DOB` DATE NOT NULL,
  38.   `salary` INT NOT NULL,
  39.   `branchNo` VARCHAR(5) NOT NULL,
  40.   PRIMARY KEY (`staffNo`),
  41.   CONSTRAINT `branchNo`
  42.     FOREIGN KEY (`branchNo`)
  43.     REFERENCES `azziedev_attendtudb`.`Branch` (`branchNo`)
  44.  
  45.  
  46. -- -----------------------------------------------------
  47. -- Table `azziedev_attendtudb`.`users`
  48. -- -----------------------------------------------------
  49. CREATE TABLE IF NOT EXISTS `azziedev_attendtudb`.`users` (
  50.   `netid` VARCHAR(20) NOT NULL,
  51.   `firstname` VARCHAR(45) NOT NULL,
  52.   `lastname` VARCHAR(45) NOT NULL,
  53.   PRIMARY KEY (`netid`))
  54.  
  55.  
  56. -- -----------------------------------------------------
  57. -- Table `azziedev_attendtudb`.`sessions`
  58. -- -----------------------------------------------------
  59. CREATE TABLE IF NOT EXISTS `azziedev_attendtudb`.`sessions` (
  60.   `idsessions` INT NOT NULL,
  61.   `creatorid` VARCHAR(20) NOT NULL,
  62.   `courseid` INT NULL,
  63.   PRIMARY KEY (`idsessions`),
  64.   INDEX `netid_idx` (`creatorid` ASC) VISIBLE,
  65.   CONSTRAINT `netid`
  66.     FOREIGN KEY (`creatorid`)
  67.     REFERENCES `azziedev_attendtudb`.`users` (`netid`)
  68.  
  69.  
  70. SET SQL_MODE=@OLD_SQL_MODE;
  71. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  72. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Paste is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.