SQL 48
JAKE FOREIGN KEY CHECK DISABLED! Guest on 17th November 2021 11:05:18 PM
  1. -- these tables need to be inserted with foreign key checks off
  2.  
  3. -- MySQL Workbench Forward Engineering
  4.  
  5. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  6. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  7. 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';
  8.  
  9. -- -----------------------------------------------------
  10. -- Schema mydb
  11. -- -----------------------------------------------------
  12. -- -----------------------------------------------------
  13. -- Schema azziedev_jake
  14. -- -----------------------------------------------------
  15.  
  16. -- -----------------------------------------------------
  17. -- Schema azziedev_jake
  18. -- -----------------------------------------------------
  19. CREATE SCHEMA IF NOT EXISTS `azziedev_jake` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
  20. USE `azziedev_jake` ;
  21.  
  22. -- -----------------------------------------------------
  23. -- Table `azziedev_jake`.`EMPLOYEE`
  24. -- -----------------------------------------------------
  25. CREATE TABLE IF NOT EXISTS `azziedev_jake`.`EMPLOYEE` (
  26.   `FNAME` VARCHAR(45) NULL DEFAULT NULL,
  27.   `MINIT` VARCHAR(45) NULL DEFAULT NULL,
  28.   `LNAME` VARCHAR(45) NULL DEFAULT NULL,
  29.   `SSN` INT(9) NOT NULL,
  30.   `BDATE` DATE NULL DEFAULT NULL,
  31.   `ADDRESS` VARCHAR(45) NULL DEFAULT NULL,
  32.   `SEX` VARCHAR(45) NULL DEFAULT NULL,
  33.   `SALARY` INT(8) NULL DEFAULT NULL,
  34.   `SUPERSSN` INT(9) NULL DEFAULT NULL,
  35.   `DNO` INT(1) NULL DEFAULT NULL,
  36.   PRIMARY KEY (`SSN`),
  37.   INDEX `SUPERSSN_idx` (`SUPERSSN` ASC),
  38.   INDEX `DNO_idx` (`DNO` ASC),
  39.   CONSTRAINT `DNO`
  40.     FOREIGN KEY (`DNO`)
  41.     REFERENCES `azziedev_jake`.`DEPARTMENT` (`DNUMBER`)
  42.     ON DELETE NO ACTION
  43.     ON UPDATE NO ACTION,
  44.   CONSTRAINT `SUPERSSN`
  45.     FOREIGN KEY (`SUPERSSN`)
  46.     REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
  47.     ON DELETE NO ACTION
  48.     ON UPDATE NO ACTION)
  49. ENGINE = InnoDB
  50. DEFAULT CHARACTER SET = utf8
  51. COLLATE = utf8_unicode_ci;
  52.  
  53.  
  54. -- -----------------------------------------------------
  55. -- Table `azziedev_jake`.`DEPARTMENT`
  56. -- -----------------------------------------------------
  57. CREATE TABLE IF NOT EXISTS `azziedev_jake`.`DEPARTMENT` (
  58.   `DNAME` VARCHAR(45) NULL DEFAULT NULL,
  59.   `DNUMBER` INT(1) NOT NULL,
  60.   `MGRSSN` INT(9) NULL DEFAULT NULL,
  61.   `MGRSTARTDATE` DATE NULL DEFAULT NULL,
  62.   PRIMARY KEY (`DNUMBER`),
  63.   INDEX `MGRSSN_idx` (`MGRSSN` ASC),
  64.   CONSTRAINT `MGRSSN`
  65.     FOREIGN KEY (`MGRSSN`)
  66.     REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
  67.     ON DELETE NO ACTION
  68.     ON UPDATE NO ACTION)
  69. ENGINE = InnoDB
  70. DEFAULT CHARACTER SET = utf8
  71. COLLATE = utf8_unicode_ci;
  72.  
  73.  
  74. -- -----------------------------------------------------
  75. -- Table `azziedev_jake`.`DEPENDENT`
  76. -- -----------------------------------------------------
  77. CREATE TABLE IF NOT EXISTS `azziedev_jake`.`DEPENDENT` (
  78.   `ESSN` INT(9) NOT NULL,
  79.   `DEPENDENT_NAME` VARCHAR(45) NOT NULL,
  80.   `SEX` VARCHAR(45) NULL DEFAULT NULL,
  81.   `BDATE` DATE NULL DEFAULT NULL,
  82.   `RELATIONSHIP` VARCHAR(45) NULL DEFAULT NULL,
  83.   PRIMARY KEY (`ESSN`, `DEPENDENT_NAME`),
  84.   CONSTRAINT `ESSN`
  85.     FOREIGN KEY (`ESSN`)
  86.     REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
  87.     ON DELETE NO ACTION
  88.     ON UPDATE NO ACTION)
  89. ENGINE = InnoDB
  90. DEFAULT CHARACTER SET = utf8
  91. COLLATE = utf8_unicode_ci;
  92.  
  93.  
  94. -- -----------------------------------------------------
  95. -- Table `azziedev_jake`.`DEPT_LOCATIONS`
  96. -- -----------------------------------------------------
  97. CREATE TABLE IF NOT EXISTS `azziedev_jake`.`DEPT_LOCATIONS` (
  98.   `DNUMBER` INT(1) NOT NULL,
  99.   `DLOCATION` VARCHAR(45) NOT NULL,
  100.   PRIMARY KEY (`DNUMBER`, `DLOCATION`),
  101.   INDEX `DEPT_idx` (`DNUMBER` ASC),
  102.   CONSTRAINT `DEPT`
  103.     FOREIGN KEY (`DNUMBER`)
  104.     REFERENCES `azziedev_jake`.`DEPARTMENT` (`DNUMBER`)
  105.     ON DELETE NO ACTION
  106.     ON UPDATE NO ACTION)
  107. ENGINE = InnoDB
  108. DEFAULT CHARACTER SET = utf8
  109. COLLATE = utf8_unicode_ci;
  110.  
  111.  
  112. -- -----------------------------------------------------
  113. -- Table `azziedev_jake`.`PROJECT`
  114. -- -----------------------------------------------------
  115. CREATE TABLE IF NOT EXISTS `azziedev_jake`.`PROJECT` (
  116.   `PNAME` VARCHAR(45) NULL DEFAULT NULL,
  117.   `PNUMBER` INT(2) NOT NULL,
  118.   `PLOCATION` VARCHAR(45) NULL DEFAULT NULL,
  119.   `DNUM` INT(1) NULL DEFAULT NULL,
  120.   PRIMARY KEY (`PNUMBER`),
  121.   INDEX `DNUM_idx` (`DNUM` ASC),
  122.   CONSTRAINT `DNUM`
  123.     FOREIGN KEY (`DNUM`)
  124.     REFERENCES `azziedev_jake`.`DEPARTMENT` (`DNUMBER`)
  125.     ON DELETE NO ACTION
  126.     ON UPDATE NO ACTION)
  127. ENGINE = InnoDB
  128. DEFAULT CHARACTER SET = utf8
  129. COLLATE = utf8_unicode_ci;
  130.  
  131.  
  132. -- -----------------------------------------------------
  133. -- Table `azziedev_jake`.`WORKS_ON`
  134. -- -----------------------------------------------------
  135. CREATE TABLE IF NOT EXISTS `azziedev_jake`.`WORKS_ON` (
  136.   `ESSN` INT(9) NOT NULL,
  137.   `PNO` INT(2) NOT NULL,
  138.   `HOURS` DECIMAL(3,1) NULL DEFAULT NULL,
  139.   PRIMARY KEY (`ESSN`, `PNO`),
  140.   INDEX `PNOw_idx` (`PNO` ASC),
  141.   CONSTRAINT `ESSNw`
  142.     FOREIGN KEY (`ESSN`)
  143.     REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
  144.     ON DELETE NO ACTION
  145.     ON UPDATE NO ACTION,
  146.   CONSTRAINT `PNOw`
  147.     FOREIGN KEY (`PNO`)
  148.     REFERENCES `azziedev_jake`.`PROJECT` (`PNUMBER`)
  149.     ON DELETE NO ACTION
  150.     ON UPDATE NO ACTION)
  151. ENGINE = InnoDB
  152. DEFAULT CHARACTER SET = utf8
  153. COLLATE = utf8_unicode_ci;
  154.  
  155.  
  156. SET SQL_MODE=@OLD_SQL_MODE;
  157. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  158. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  159.  
  160.  
  161.  
  162.  
  163. -- these need to be inserted with foreign key checks off
  164. INSERT INTO `EMPLOYEE` VALUES ('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Houston TX', 'M', '25000', NULL, '1');
  165. INSERT INTO `EMPLOYEE` VALUES ('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss Houston TX', 'M', '40000', '888665555', '5');
  166. INSERT INTO `EMPLOYEE` VALUES ('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry Bellaire TX', 'F', '43000', '888665555', '4');

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.

Copyright © 2026 Paste. All rights reserved.
Powered by Paste