Having trouble adding a foreign key in HeidiSQL (Error 1215)

Question

I've tried just about everything but I'm getting error 1215 when trying to create a foreign key in a child table I have. Here are my tables:

CREATE TABLE `Con` (
  `ConID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Website` varchar(500) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `FirstYear` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`ConID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `ConEvent` (
  `EventID` int(11) NOT NULL AUTO_INCREMENT,
  `ConID` int(11) NOT NULL,
  `DateStart` date DEFAULT NULL,
  `DateEnd` date DEFAULT NULL,
  `Year` tinyint(4) DEFAULT NULL,
  `Venue` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Address` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `City` tinytext COLLATE utf8_unicode_ci,
  `StateProvince` tinytext COLLATE utf8_unicode_ci,
  `Country` tinytext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`EventID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Here is my syntax:

ALTER TABLE ConEvent
ADD FOREIGN KEY (ConID) REFERENCES Con(ConID);

I can't SHOW ENGINE INNODB STATUS; because I'm not a super user (error 1227). I tried to make myself one but was unable to.

The alter statement that you have looks ok to me, but in the `ConEvent` table, an index is needed on the `ConID` column. — Paul T., Oct 11 '21 at 10:59
@PaulT. I added a key to ConID in the ConEvent table and it still doesn't work. — SageLeader, Oct 11 '21 at 17:09
@PaulT. I was on 10.2 but I just upgraded to 11.3 and it still doesn't work. Same 1215 error. — SageLeader, Oct 12 '21 at 01:57
Ok, I just downloaded heidisql 11.3 and it worked for me with the added index, see this [image example](https://imgbox.com/qdpxrZqa). Are you sure that there's no SQL error? ... I did have to add `;` after each `CREATE` SQL statement that you provided, but I did NOT try the `ALTER` statement. — Paul T., Oct 12 '21 at 03:00
The tables are already created so maybe that's the issue? It seems like I should be able to add it after the fact. The error is from the ALTER statement. Actually how do you add an index? All I see is how to add a key. — SageLeader, Oct 12 '21 at 05:06
Yes, after the fact should be possible too. I'll try that as well, though it will be much later today before I will be able to try. — Paul T., Oct 12 '21 at 12:16
Still fighting the FK create with alter, but to answer your question about adding an index ... `KEY` and `INDEX` are synonyms of each other, so using `INDEX` will show created with `KEY`, so doing either `ALTER TABLE conevent ADD INDEX (\`ConID\`);` or `ALTER TABLE conevent ADD KEY (\`ConID\`);` will accomplish the same goal. — Paul T., Oct 13 '21 at 02:07
Ok, so I don't know what the hell it is about `con`, but I renamed your table to `conz`, ran the `ALTER TABLE ConEvent ADD FOREIGN KEY (\`ConID\`) REFERENCES Conz(\`ConID\`);` SQL (note `conz` in the references clause), and the FK created! The `con` references that I found in [their source code](https://github.com/HeidiSQL/HeidiSQL/search?q=con), is part of references used for syntax highlighting, but not sure why `con` causes trouble. Oh well, mystery solved. (surprised that creating a table named `con` didn't error as well) — Paul T., Oct 13 '21 at 03:02

Having trouble adding a foreign key in HeidiSQL (Error 1215)

0 Answers0