I needed to link 2 tables in MySQL and i thought it would be soooo easy, but it turned out it’s not. I was getting a lot of weird errors but the one in the title was the most persistent.
Of course i took notes and all that but it just amases me why it eventually worked and i don’t know how. Most probably a mispeled syntax. 😀
This post proved to be helpful a bit (i know have an idea on how things should be when playing with foreign keys) – it’s about the most common reasons for why you get that error. I tried all the solutions there but nothing really worked.
So how did i managed to make it work? Well i sat down, drew the tables on a piece of paper and i constantly reminded myself what i want to do and what the steps would be. MySQL is really crappy with its syntax (at least for me) and of course that contributed to the amount of time spent on this.
I have 2 tables – “exercises” and “bodypart”. My idea is that when i insert data in the “exercises” i want the :
- “exID” to auto increment itself
- “exNAME” to be wrote down manually
- “exBP” to contain only the stuff that are in another table – the “bodypart” table
This is the syntax that worked for me:
[code lang=”sql”]
CREATE TABLE IF NOT EXISTS bodypart(
bpBP VARCHAR(15),
PRIMARY KEY(bpBP)
) ENGINE=INNODB;CREATE TABLE IF NOT EXISTS exercises(
exID INT UNSIGNED NOT NULL AUTO_INCREMENT,
exName VARCHAR(255) NOT NULL UNIQUE,
exBP VARCHAR(15),
PRIMARY KEY(exID),
FOREIGN KEY(exBP) REFERENCES bodypart(bpBP)
) ENGINE=INNODB;
[/code]