Error code 1215 with same datatype?

Question

CREATE SCHEMA IF NOT EXISTS `cap` DEFAULT CHARACTER SET utf8 ;
USE `cap` ;

-- -----------------------------------------------------
-- Table `capstone`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`users` (
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  `roleid` INT NOT NULL,
  `fullname` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NULL,
  `phone` VARCHAR(45) NULL,
  `department` VARCHAR(45) NULL,
  PRIMARY KEY (`username`, `roleid`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`capstone`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`capstone` (
  `username` VARCHAR(45) NOT NULL,
  `capstoneid` INT NOT NULL,
  `typeid` INT NOT NULL,
  `title` VARCHAR(45) NULL,
  `abstract` MEDIUMTEXT NULL,
  `plagerismscore` VARCHAR(45) NULL,
  `grade` VARCHAR(45) NULL,
  PRIMARY KEY (`username`, `capstoneid`, `typeid`),
  INDEX `fk_capstone_users_idx` (`username` ASC),
  CONSTRAINT `fk_capstone_users`
    FOREIGN KEY (`username`)
    REFERENCES `cap`.`users` (`username`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`committee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`committee` (
  `capstoneid` INT NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  `has_accepted` TINYINT NULL,
  `has_declined` TINYINT NULL,
  `positionid` INT NOT NULL,
  `tracking` TINYINT NULL,
  PRIMARY KEY (`capstoneid`, `username`, `positionid`),
  CONSTRAINT `fk_committee_capstone`
    FOREIGN KEY (`capstoneid`)
    REFERENCES `cap`.`capstone` (`capstoneid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`studentdetails`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`studentdetails` (
  `username` VARCHAR(45) NOT NULL,
  `mastersstart` VARCHAR(45) NULL,
  `capstonestart` VARCHAR(45) NULL,
  PRIMARY KEY (`username`),
  CONSTRAINT `fk_studentdetails_users`
    FOREIGN KEY (`username`)
    REFERENCES `cap`.`users` (`username`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`ritcalendar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`ritcalendar` (
  `term` INT NOT NULL,
  `startdate` VARCHAR(45) NULL,
  `adddropdeadline` VARCHAR(45) NULL,
  `gradedeadline` VARCHAR(45) NULL,
  `enddate` VARCHAR(45) NULL,
  PRIMARY KEY (`term`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`statushistory`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`statushistory` (
  `capstoneid` INT NOT NULL,
  `statusid` INT NOT NULL,
  `date` DATETIME(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`capstoneid`, `statusid`),
  UNIQUE INDEX `capstoneid_UNIQUE` (`capstoneid` ASC),
  CONSTRAINT `fk_statushistory_capstone`
    FOREIGN KEY (`capstoneid`)
    REFERENCES `cap`.`capstone` (`capstoneid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`status` (
  `statusid` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `stepcode` VARCHAR(45) NULL,
  `description` VARCHAR(255) NULL,
  PRIMARY KEY (`statusid`),
  CONSTRAINT `fk_status_statushistory`
    FOREIGN KEY (`statusid`)
    REFERENCES `cap`.`statushistory` (`statusid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`roles` (
  `roleid` INT NOT NULL,
  `role` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`roleid`),
  UNIQUE INDEX `role_UNIQUE` (`role` ASC),
  CONSTRAINT `fk_roles_users`
    FOREIGN KEY (`roleid`)
    REFERENCES `cap`.`users` (`roleid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`types` (
  `typeid` INT NOT NULL,
  `type` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`typeid`),
  CONSTRAINT `fk_types_capstone`
    FOREIGN KEY (`typeid`)
    REFERENCES `cap`.`capstone` (`typeid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `capstone`.`positions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cap`.`positions` (
  `positionid` INT NOT NULL,
  `position` VARCHAR(45) NULL,
  PRIMARY KEY (`positionid`),
  CONSTRAINT `fk_postions_committee`
    FOREIGN KEY (`positionid`)
    REFERENCES `cap`.`committee` (`positionid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `cap`.`committee` (   `capstoneid` INT NOT NULL,   `username` VARCHAR(45) NOT NULL,   `has_accepted` TINYINT NULL,   `has_declined` TINYINT NULL,   `positionid` INT NOT NULL,   `tracking` TINYINT NULL,   PRIMARY KEY (`capstoneid`, `username`, `positionid`),   CONSTRAINT `fk_committee_capstone`     FOREIGN KEY (`capstoneid`)     REFERENCES `cap`.`capstone` (`capstoneid`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB    Error Code: 1215. Cannot add foreign key constraint 0.031 sec

Can someone explain why I keep getting this error? They are the same datatype...

Pls indicate where you get the error and the full error message as well. — Shadow, Apr 15 '18 at 17:07

score 0 · Answer 1 · answered Apr 15 '18 at 17:07

You should be more specific when you ask a question. You have eight foreign keys, you should at least say which one is causing an error.

Better would be to reduce your question to just the two tables involved in the error: capstone and committee

Here's what I see:

CREATE TABLE IF NOT EXISTS `cap`.`capstone` (
  ...
  PRIMARY KEY (`username`, `capstoneid`, `typeid`),

CREATE TABLE IF NOT EXISTS `cap`.`committee` (
  ...
    FOREIGN KEY (`capstoneid`)
    REFERENCES `cap`.`capstone` (`capstoneid`)

You have a FK in committee that is referencing the middle column of the three-column primary key in capstone.

You should always make the FK have the same columns as the primary key it references.

Error code 1215 with same datatype?

1 Answers1