CODEWITHSUNDEEP
mysql
Mitchell Gardepe
Mitchell Gardepe

Reputation: 41

"key column doesn't exist in table"

I'm making a database in MySQL in class and I'm having trouble adding foreign keys to my tables. I already added most of the foreign keys to my tables when I created the tables but obviously I couldn't add all of them in the creation process. I'm trying to add the remaining foreign keys with the below method.

ALTER TABLE ORDERS 
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

For some reason the error message listed in the title keeps popping up. The code is below. I can see the tables are coming out a bit funny but if you read them from left to right you can see what it says. What do you think? 

mysql> show columns from games;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| gameID     | int(11)     | NO   | PRI | NULL    | auto_increment |
| videoGames | varchar(30) | NO   |     | NULL    |                |
| year       | int(11)     | NO   |     | NULL    |                |
| genreID    | int(11)     | NO   | MUL | NULL    |                |
| companyID  | int(11)     | NO   | MUL | NULL    |                |
| directorID | int(11)     | NO   | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> show columns from consoles;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| consoleID    | int(11)     | NO   | PRI | NULL    | auto_increment |
| console      | varchar(20) | NO   |     | NULL    |                |
| yearReleased | int(11)     | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> alter table games
    -> add foreign key(consoleID) references consoles(consoleID);
ERROR 1072 (42000): Key column 'consoleID' doesn't exist in table

Upvotes: 0

Views: 4606

Answers (1)

fdglefevre
fdglefevre

Reputation: 692

There is no field consoleID in your table games. You need to create it before trying to add a constraint on it. This will add consoleID and create the constraint:

ALTER TABLE games ADD COLUMN consoleID INTEGER NOT NULL;
ALTER TABLE games ADD FOREIGN KEY (consoleID) REFERENCES consoles(consoleID);

You should think about adding a third table where you associate consoles with games because some games are multiplateform. :)

Upvotes: 1

Related Questions