MySQL Forums
Forum List  »  InnoDB

Re: Foreign Key problems
Posted by: Paul Reiser
Date: September 14, 2014 07:40PM

Hi Peter - here is the output from forward engineering on MySQL workbench.

Thanks very much for any help...
Paul

PS - the indentation did not come thru, any suggestions as to making the code indent?


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 gen2
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `gen2` DEFAULT CHARACTER SET utf8 ;
USE `gen2` ;

-- -----------------------------------------------------
-- Table `gen2`.`SOURCES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gen2`.`SOURCES` (
`SOUR` VARCHAR(32) NOT NULL,
`AUTH` VARCHAR(32) NOT NULL,
`TITL` VARCHAR(32) NULL DEFAULT NULL,
PRIMARY KEY (`SOUR`),
INDEX `SOUR_AUTH.idx` (`SOUR` ASC, `AUTH` ASC),
CONSTRAINT `SOUR-AUTH`
FOREIGN KEY (`SOUR` , `AUTH`)
REFERENCES `gen2`.`PEOPLE` (`SOUR` , `INDI`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `gen2`.`PEOPLE`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gen2`.`PEOPLE` (
`INDI` VARCHAR(32) NOT NULL,
`SOUR` VARCHAR(32) NOT NULL,
`SURN` VARCHAR(32) NULL DEFAULT NULL,
`NAM1` VARCHAR(32) NULL DEFAULT NULL,
`NAM2` VARCHAR(32) NULL DEFAULT NULL,
`PARE` VARCHAR(32) NULL DEFAULT NULL,
PRIMARY KEY (`INDI`, `SOUR`),
INDEX `SOUR_idx` (`SOUR` ASC),
INDEX `PARE_idx` (`PARE` ASC, `SOUR` ASC),
CONSTRAINT `INDI_PARE`
FOREIGN KEY (`PARE` , `SOUR`)
REFERENCES `gen2`.`PARENTS` (`PARE` , `SOUR`)
ON UPDATE CASCADE,
CONSTRAINT `INDI_SOUR`
FOREIGN KEY (`SOUR`)
REFERENCES `gen2`.`SOURCES` (`SOUR`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `gen2`.`PARENTS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gen2`.`PARENTS` (
`PARE` VARCHAR(32) NOT NULL,
`SOUR` VARCHAR(32) NOT NULL,
`PARE1` VARCHAR(32) NULL DEFAULT NULL,
`PARE2` VARCHAR(32) NULL DEFAULT NULL,
PRIMARY KEY (`PARE`, `SOUR`),
INDEX `SOUR_idx` (`SOUR` ASC),
INDEX `PARE1_idx` (`PARE1` ASC, `SOUR` ASC),
INDEX `PARE2_idx` (`PARE2` ASC, `SOUR` ASC),
CONSTRAINT `PARE_1`
FOREIGN KEY (`PARE1` , `SOUR`)
REFERENCES `gen2`.`PEOPLE` (`INDI` , `SOUR`)
ON UPDATE CASCADE,
CONSTRAINT `PARE_2`
FOREIGN KEY (`PARE2` , `SOUR`)
REFERENCES `gen2`.`PEOPLE` (`INDI` , `SOUR`)
ON UPDATE CASCADE,
CONSTRAINT `PARE_SOUR`
FOREIGN KEY (`SOUR`)
REFERENCES `gen2`.`SOURCES` (`SOUR`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


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

Options: ReplyQuote


Subject
Views
Written By
Posted
1577
September 12, 2014 10:46PM
825
September 13, 2014 09:18PM
859
September 14, 2014 02:39AM
864
September 14, 2014 01:15PM
Re: Foreign Key problems
1127
September 14, 2014 07:40PM
888
September 14, 2014 09:37PM
887
September 14, 2014 11:07PM
938
September 15, 2014 07:26AM
1065
September 15, 2014 11:08AM
878
September 15, 2014 03:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.