1

I have these two tables called "Timelines" and "Universes". In "Timelines" I have a column named "ID" which must be a combination of a Timeline's ID after the ID of its related Universe. For that reason I created a Stored Virtual Column to be the Primary key of "Timelines" table, called "ID".
This "ID" must be a combination of two other columns in the same table, "UniverseID" and "TimelineID", So the expression for Virtual Column ("ID") is :

CONCAT(UniverseID, ':', TimelineID)

But, UniverseID MUST refer to the column "ID" in the table "Universes". So I try to make it a Foreign key. But there's a problem. I can't. Here's what I try to do :

ALTER TABLE `Timelines` ADD  CONSTRAINT `Timelines__UniverseID` FOREIGN KEY (`UniverseID`) REFERENCES `Universes`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

And I get this error :

 #1215 - Cannot add foreign key constraint

There is NO OTHER FOREIGN KEY with that name in any other table !

What should I do ? Is there a problem with my Design ? Or is there another way to get the same result ?

  • 1
    Please add your table definitions as text to your question. – P.Salmon Dec 26 '17 at 09:20
  • Possible duplicate of https://stackoverflow.com/q/5835978/3404097 – philipxy Dec 26 '17 at 22:56
  • Hi. Read the manual about declaring FK constraints. You are not following the rules & you don't understand what a FK is: when subrow values somewhere also appear elsewhere. That is not what you have. Also [smart keys are a bad idea](https://stackoverflow.com/a/28454136/3404097). You want a composite CK. – philipxy Dec 26 '17 at 22:59
  • Possible duplicate of [How to properly create composite primary keys - MYSQL](https://stackoverflow.com/questions/5835978/how-to-properly-create-composite-primary-keys-mysql) –  Dec 28 '17 at 08:06

0 Answers0