JAKE FOREIGN KEY CHECK DISABLED!

MARKDOWN 8

JAKE FOREIGN KEY CHECK DISABLED! Guest on 17th November 2021 11:05:03 PM

-- these tables need to be inserted with foreign key checks off

-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- Schema mydb

-- Schema azziedev_jake

CREATE SCHEMA IF NOT EXISTS azziedev_jake DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ; USE azziedev_jake ;

-- Table azziedev_jake.EMPLOYEE

CREATE TABLE IF NOT EXISTS azziedev_jake.EMPLOYEE ( FNAME VARCHAR(45) NULL DEFAULT NULL, MINIT VARCHAR(45) NULL DEFAULT NULL, LNAME VARCHAR(45) NULL DEFAULT NULL, SSN INT(9) NOT NULL, BDATE DATE NULL DEFAULT NULL, ADDRESS VARCHAR(45) NULL DEFAULT NULL, SEX VARCHAR(45) NULL DEFAULT NULL, SALARY INT(8) NULL DEFAULT NULL, SUPERSSN INT(9) NULL DEFAULT NULL, DNO INT(1) NULL DEFAULT NULL, PRIMARY KEY (SSN), INDEX SUPERSSN_idx (SUPERSSN ASC), INDEX DNO_idx (DNO ASC), CONSTRAINT DNO FOREIGN KEY (DNO) REFERENCES azziedev_jake.DEPARTMENT (DNUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT SUPERSSN FOREIGN KEY (SUPERSSN) REFERENCES azziedev_jake.EMPLOYEE (SSN) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- Table azziedev_jake.DEPARTMENT

CREATE TABLE IF NOT EXISTS azziedev_jake.DEPARTMENT ( DNAME VARCHAR(45) NULL DEFAULT NULL, DNUMBER INT(1) NOT NULL, MGRSSN INT(9) NULL DEFAULT NULL, MGRSTARTDATE DATE NULL DEFAULT NULL, PRIMARY KEY (DNUMBER), INDEX MGRSSN_idx (MGRSSN ASC), CONSTRAINT MGRSSN FOREIGN KEY (MGRSSN) REFERENCES azziedev_jake.EMPLOYEE (SSN) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- Table azziedev_jake.DEPENDENT

CREATE TABLE IF NOT EXISTS azziedev_jake.DEPENDENT ( ESSN INT(9) NOT NULL, DEPENDENT_NAME VARCHAR(45) NOT NULL, SEX VARCHAR(45) NULL DEFAULT NULL, BDATE DATE NULL DEFAULT NULL, RELATIONSHIP VARCHAR(45) NULL DEFAULT NULL, PRIMARY KEY (ESSN, DEPENDENT_NAME), CONSTRAINT ESSN FOREIGN KEY (ESSN) REFERENCES azziedev_jake.EMPLOYEE (SSN) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- Table azziedev_jake.DEPT_LOCATIONS

CREATE TABLE IF NOT EXISTS azziedev_jake.DEPT_LOCATIONS ( DNUMBER INT(1) NOT NULL, DLOCATION VARCHAR(45) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATION), INDEX DEPT_idx (DNUMBER ASC), CONSTRAINT DEPT FOREIGN KEY (DNUMBER) REFERENCES azziedev_jake.DEPARTMENT (DNUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- Table azziedev_jake.PROJECT

CREATE TABLE IF NOT EXISTS azziedev_jake.PROJECT ( PNAME VARCHAR(45) NULL DEFAULT NULL, PNUMBER INT(2) NOT NULL, PLOCATION VARCHAR(45) NULL DEFAULT NULL, DNUM INT(1) NULL DEFAULT NULL, PRIMARY KEY (PNUMBER), INDEX DNUM_idx (DNUM ASC), CONSTRAINT DNUM FOREIGN KEY (DNUM) REFERENCES azziedev_jake.DEPARTMENT (DNUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

-- Table azziedev_jake.WORKS_ON

CREATE TABLE IF NOT EXISTS azziedev_jake.WORKS_ON ( ESSN INT(9) NOT NULL, PNO INT(2) NOT NULL, HOURS DECIMAL(3,1) NULL DEFAULT NULL, PRIMARY KEY (ESSN, PNO), INDEX PNOw_idx (PNO ASC), CONSTRAINT ESSNw FOREIGN KEY (ESSN) REFERENCES azziedev_jake.EMPLOYEE (SSN) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT PNOw FOREIGN KEY (PNO) REFERENCES azziedev_jake.PROJECT (PNUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- these need to be inserted with foreign key checks off INSERT INTO EMPLOYEE VALUES ('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Houston TX', 'M', '25000', NULL, '1'); INSERT INTO EMPLOYEE VALUES ('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss Houston TX', 'M', '40000', '888665555', '5'); INSERT INTO EMPLOYEE VALUES ('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry Bellaire TX', 'F', '43000', '888665555', '4');

Raw Paste

-- these tables need to be inserted with foreign key checks off

-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
 
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema azziedev_jake
-- -----------------------------------------------------
 
-- -----------------------------------------------------
-- Schema azziedev_jake
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `azziedev_jake` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `azziedev_jake` ;
 
-- -----------------------------------------------------
-- Table `azziedev_jake`.`EMPLOYEE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jake`.`EMPLOYEE` (
  `FNAME` VARCHAR(45) NULL DEFAULT NULL,
  `MINIT` VARCHAR(45) NULL DEFAULT NULL,
  `LNAME` VARCHAR(45) NULL DEFAULT NULL,
  `SSN` INT(9) NOT NULL,
  `BDATE` DATE NULL DEFAULT NULL,
  `ADDRESS` VARCHAR(45) NULL DEFAULT NULL,
  `SEX` VARCHAR(45) NULL DEFAULT NULL,
  `SALARY` INT(8) NULL DEFAULT NULL,
  `SUPERSSN` INT(9) NULL DEFAULT NULL,
  `DNO` INT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`SSN`),
  INDEX `SUPERSSN_idx` (`SUPERSSN` ASC),
  INDEX `DNO_idx` (`DNO` ASC),
  CONSTRAINT `DNO`
    FOREIGN KEY (`DNO`)
    REFERENCES `azziedev_jake`.`DEPARTMENT` (`DNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `SUPERSSN`
    FOREIGN KEY (`SUPERSSN`)
    REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_jake`.`DEPARTMENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jake`.`DEPARTMENT` (
  `DNAME` VARCHAR(45) NULL DEFAULT NULL,
  `DNUMBER` INT(1) NOT NULL,
  `MGRSSN` INT(9) NULL DEFAULT NULL,
  `MGRSTARTDATE` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`DNUMBER`),
  INDEX `MGRSSN_idx` (`MGRSSN` ASC),
  CONSTRAINT `MGRSSN`
    FOREIGN KEY (`MGRSSN`)
    REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_jake`.`DEPENDENT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jake`.`DEPENDENT` (
  `ESSN` INT(9) NOT NULL,
  `DEPENDENT_NAME` VARCHAR(45) NOT NULL,
  `SEX` VARCHAR(45) NULL DEFAULT NULL,
  `BDATE` DATE NULL DEFAULT NULL,
  `RELATIONSHIP` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`ESSN`, `DEPENDENT_NAME`),
  CONSTRAINT `ESSN`
    FOREIGN KEY (`ESSN`)
    REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_jake`.`DEPT_LOCATIONS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jake`.`DEPT_LOCATIONS` (
  `DNUMBER` INT(1) NOT NULL,
  `DLOCATION` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`DNUMBER`, `DLOCATION`),
  INDEX `DEPT_idx` (`DNUMBER` ASC),
  CONSTRAINT `DEPT`
    FOREIGN KEY (`DNUMBER`)
    REFERENCES `azziedev_jake`.`DEPARTMENT` (`DNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_jake`.`PROJECT`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jake`.`PROJECT` (
  `PNAME` VARCHAR(45) NULL DEFAULT NULL,
  `PNUMBER` INT(2) NOT NULL,
  `PLOCATION` VARCHAR(45) NULL DEFAULT NULL,
  `DNUM` INT(1) NULL DEFAULT NULL,
  PRIMARY KEY (`PNUMBER`),
  INDEX `DNUM_idx` (`DNUM` ASC),
  CONSTRAINT `DNUM`
    FOREIGN KEY (`DNUM`)
    REFERENCES `azziedev_jake`.`DEPARTMENT` (`DNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
-- -----------------------------------------------------
-- Table `azziedev_jake`.`WORKS_ON`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `azziedev_jake`.`WORKS_ON` (
  `ESSN` INT(9) NOT NULL,
  `PNO` INT(2) NOT NULL,
  `HOURS` DECIMAL(3,1) NULL DEFAULT NULL,
  PRIMARY KEY (`ESSN`, `PNO`),
  INDEX `PNOw_idx` (`PNO` ASC),
  CONSTRAINT `ESSNw`
    FOREIGN KEY (`ESSN`)
    REFERENCES `azziedev_jake`.`EMPLOYEE` (`SSN`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `PNOw`
    FOREIGN KEY (`PNO`)
    REFERENCES `azziedev_jake`.`PROJECT` (`PNUMBER`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- these need to be inserted with foreign key checks off
INSERT INTO `EMPLOYEE` VALUES ('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Houston TX', 'M', '25000', NULL, '1');
INSERT INTO `EMPLOYEE` VALUES ('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss Houston TX', 'M', '40000', '888665555', '5');
INSERT INTO `EMPLOYEE` VALUES ('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry Bellaire TX', 'F', '43000', '888665555', '4');