CODEWITHSUNDEEP
mysql
user10176607
user10176607

Reputation:

Foreign Key constraint error even without a foreign key

I am getting the following error:

Error Code: 1215. Cannot add foreign key constraint

This happens even if I remove all foreign key constraints. timeline table does exist in the database, so that is not the issue.

Can't seem to figure out what would cause this problem 

USE study;

CREATE TABLE IF NOT EXISTS timelineStage(
  timelineStageID INT NOT NULL AUTO_INCREMENT,
  timelineID TINYINT NOT NULL,
  stageName VARCHAR(100) NOT NULL,
  stagePredecessorID INT NULL,
  isStartup TINYINT NOT NULL DEFAULT 0,
  timelineStageNotes VARCHAR(500) NULL,
  recCreatedByUserID INT NOT NULL,
  recCreatedTimeUTC TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  recUpdatedByUserID INT NOT NULL,
  recUpdatedTimeUTC TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (timelineStageID),
  UNIQUE KEY IX_U_timelineStage_stageName(stageName, timelineID),
  UNIQUE KEY IX_U_timelineStage_stagePredecessor(stagePredecessorID, timelineID),
  CONSTRAINT FK_timelineStage_timelineID
    FOREIGN KEY (timelineID)
    REFERENCES timeline(timelineID)
    ON UPDATE CASCADE,
  CONSTRAINT FK_timelineStage_stagePredecessorID
    FOREIGN KEY (stagePredecessorID)
    REFERENCES timelineStage(timelineStageID)
    ON UPDATE CASCADE,
  CONSTRAINT FK_timelineStage_recCreatedByUserID
    FOREIGN KEY (recCreatedByUserID)
    REFERENCES customer.user(userID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT FK_timelineStage_recUpdatedByUserID
    FOREIGN KEY (recUpdatedByUserID)
    REFERENCES customer.user(userID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)

ADDING Timeline and customer.user

USE study;

CREATE TABLE timeline(
   timelineID TINYINT NOT NULL AUTO_INCREMENT,
   timelineName VARCHAR(100) NOT NULL,
   timelineNotes    VARCHAR(500) NULL,
   recCreatedByUserID INT(11) NOT NULL,
   recCreatedTimeUTC DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   recUpdatedByUserID INT(11) NOT NULL,
   recUpdatedTimeUTC TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (timelineID)
 )


CREATE TABLE customer.user(
   userID INT NOT NULL AUTO_INCREMENT,
   firstName VARCHAR(100) NOT NULL,
   lastName VARCHAR(100) NOT NULL,
   emailAddress VARCHAR(100) NOT NULL,
  PRIMARY KEY (userID))

Upvotes: 0

Views: 738

Answers (3)

user10176607
user10176607

Reputation:

The problem was with neither of the other tables. It was is study table (another table).

timelineStageID was set to TINYINT and in timelineStage table timelineStageID was set to INT.

Matching all to INT fixed the problem.

Upvotes: 1

Razvan Socol
Razvan Socol

Reputation: 5684

The problem is about this code: REFERENCES customer.user

You cannot create a foreign key constraint referencing a table in another database.

Upvotes: 0

Heiko Folkerts home
Heiko Folkerts home

Reputation: 76

Carefully check every references clause whether the referenced column really is present in the table. Also try to first create the table and then add the constraints one by one so you exactly know which one failed.

Upvotes: 0

Related Questions