0

I am trying to make a junction table, but I getting a ERROR 1005 errno 150

CREATE TABLE 61furiousFistPokemon(
cardNumber int NOT NULL PRIMARY KEY,
pokemonName VARCHAR(12),
type VARCHAR(10),
stage VARCHAR(10),
evolvesFrom VARCHAR(12),
HP INT,
retreatCost INT,
weakness VARCHAR(10),
weaknessAmount VARCHAR(5),
resistance VARCHAR(5),
resistanceAmount VARCHAR(5)
);

CREATE TABLE cardTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
tagName VARCHAR(60) NOT NULL
);

CREATE TABLE 61furiousFistPokemonTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
pokemonCardNumber int,
tagName VARCHAR(60),
FOREIGN KEY (pokemonCardNumber) REFERENCES 61furiousFistPokemon(cardNumber),
FOREIGN KEY (tagName) REFERENCES cardTags(tagName)
);

Any help would be greatly appreciated.

1

tagName column in table cardTags is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName).

  • Although you did not provide a solution, +1 for revealing root cause. – RolandoMySQLDBA Oct 17 '14 at 15:19
0

Very often it happens, when the foreign key and the reference key don't have same type or same length. Click here for more details.

0

Here is the quick fix: Just add a UNIQUE KEY on tagName in the cardTags table

SAMPLE SCRIPT

DROP DATABASE IF EXISTS testing;
CREATE DATABASE testing;
USE testing
CREATE TABLE 61furiousFistPokemon(
cardNumber int NOT NULL PRIMARY KEY,
pokemonName VARCHAR(12),
type VARCHAR(10),
stage VARCHAR(10),
evolvesFrom VARCHAR(12),
HP INT,
retreatCost INT,
weakness VARCHAR(10),
weaknessAmount VARCHAR(5),
resistance VARCHAR(5),
resistanceAmount VARCHAR(5)
);
CREATE TABLE cardTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
tagName VARCHAR(60) NOT NULL,
UNIQUE KEY (tagName)
);
CREATE TABLE 61furiousFistPokemonTags (
tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
pokemonCardNumber int,
tagName VARCHAR(60),
FOREIGN KEY (pokemonCardNumber) REFERENCES 61furiousFistPokemon(cardNumber),
FOREIGN KEY (tagName) REFERENCES cardTags(tagName)
);

SAMPLE SCRIPT EXECUTED

mysql> DROP DATABASE IF EXISTS testing;
Query OK, 3 rows affected (0.02 sec)

mysql> CREATE DATABASE testing;
Query OK, 1 row affected (0.00 sec)

mysql> USE testing
Database changed
mysql> CREATE TABLE 61furiousFistPokemon(
    -> cardNumber int NOT NULL PRIMARY KEY,
    -> pokemonName VARCHAR(12),
    -> type VARCHAR(10),
    -> stage VARCHAR(10),
    -> evolvesFrom VARCHAR(12),
    -> HP INT,
    -> retreatCost INT,
    -> weakness VARCHAR(10),
    -> weaknessAmount VARCHAR(5),
    -> resistance VARCHAR(5),
    -> resistanceAmount VARCHAR(5)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE cardTags (
    -> tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> tagName VARCHAR(60) NOT NULL,
    -> UNIQUE KEY (tagName)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE 61furiousFistPokemonTags (
    -> tagId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> pokemonCardNumber int,
    -> tagName VARCHAR(60),
    -> FOREIGN KEY (pokemonCardNumber) REFERENCES 61furiousFistPokemon(cardNumber),
    -> FOREIGN KEY (tagName) REFERENCES cardTags(tagName)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>

GIVE IT A TRY !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy