Table not found in mySQL using SQL script

Question

I'm writing a mySQL script to create a database about musicians and bands. Many musicians can be in 1 band and many bands have 1 genre. I've made the first table called musicians with the attributes musician_name and band_name successfully, with band_name as a primary key. Now I'm trying to create the second table called bands, which will have the attributes band_name and genre, using band_name as the primary key and also as a foreign key referencing band_name from the musicians table. When I run my script in MySQL Command Line Client, the script will display the first table, but then I receive multiple lines of errors that all say "ERROR 1146 (42S02): Table 'tours.bands' doesn't exist". I've created the referenced table before the referencing table, both data types of band_name are the same in both tables, and band_name includes values which are all unique, so I'm not sure what I'm doing wrong.

Here is my SQL Script:

DROP DATABASE IF EXISTS tours;
CREATE DATABASE tours;
USE tours;

CREATE TABLE musicians(
    musician_name varchar(30),
    band_name varchar(30),
    PRIMARY KEY(musician_name)
);

CREATE TABLE bands(
    band_name varchar(30),
    genre varchar(30),
    PRIMARY KEY (band_name),
    FOREIGN KEY (band_name) REFERENCES musicians(band_name)
);

INSERT INTO musicians (musician_name, band_name) VALUES ('Robinson', 'Purple Death');
INSERT INTO musicians (musician_name, band_name) VALUES ('Harvey', 'Purple Death');
INSERT INTO musicians (musician_name, band_name) VALUES ('Jackson', 'Purple Death');
INSERT INTO musicians (musician_name, band_name) VALUES ('Lynne', 'Megapain');
INSERT INTO musicians (musician_name, band_name) VALUES ('Perry', 'Megapain');
INSERT INTO musicians (musician_name, band_name) VALUES ('Hook', 'Megapain');
INSERT INTO musicians (musician_name, band_name) VALUES ('Matthews', 'Old Strawberry');
INSERT INTO musicians (musician_name, band_name) VALUES ('Lowden', 'Old Strawberry');
INSERT INTO musicians (musician_name, band_name) VALUES ('Bowyer', 'Old Strawberry');
INSERT INTO musicians (musician_name, band_name) VALUES ('Mills', 'Bakers Dozen');
INSERT INTO musicians (musician_name, band_name) VALUES ('Watson', 'Bakers Dozen');
INSERT INTO musicians (musician_name, band_name) VALUES ('Weston', 'Sarabelle Weston');
INSERT INTO musicians (musician_name, band_name) VALUES ('D Smith', 'Dimity Smith');
INSERT INTO musicians (musician_name, band_name) VALUES ('A Smith', 'Dimity Smith');
INSERT INTO musicians (musician_name, band_name) VALUES ('Atkins', 'Old Storm Town');
INSERT INTO musicians (musician_name, band_name) VALUES ('Ambrose', 'Old Storm Town');
INSERT INTO musicians (musician_name, band_name) VALUES ('Jones', 'Old Storm Town');
INSERT INTO musicians (musician_name, band_name) VALUES ('Williams', 'Horizons');
INSERT INTO musicians (musician_name, band_name) VALUES ('Moore', 'Horizons');
INSERT INTO musicians (musician_name, band_name) VALUES ('Tyler', 'Horizons');

SELECT * FROM musicians;

INSERT INTO bands (band_name, genre) VALUES ('Purple Death', 'Heavy Metal'); 
INSERT INTO bands (band_name, genre) VALUES ('Megapain', 'Heavy Metal'); 
INSERT INTO bands (band_name, genre) VALUES ('Old Strawberry', 'Folk'); 
INSERT INTO bands (band_name, genre) VALUES ('Bakers Dozen', 'Folk'); 
INSERT INTO bands (band_name, genre) VALUES ('Sarabelle Weston', 'Country and Western'); 
INSERT INTO bands (band_name, genre) VALUES ('Dimity Smith', 'Country and Western'); 
INSERT INTO bands (band_name, genre) VALUES ('Old Storm Town', 'Rock'); 
INSERT INTO bands (band_name, genre) VALUES ('Horizons', 'Rock'); 

SELECT * FROM bands;

The table bands should look like:

Bands          |  Genre
Purple Death   | Heavy Metal
Megapain       | Heavy Metal
Old Strawberry | Folk
Bakers Dozen   | Folk

etc...

Would appreciate any help as to why this table will not display and why mySQL thinks the table doesn't exist! Thanks.

EDIT: I found the issue was actually about a missing INDEX on the referenced table.

The reason why you're getting the errors is that most probably table bands was not created. List all the tables from the database: show tables, then run only the part for creating bands table (if it is actually not there) and show us the error (if any). Then we can try to help. I've run through your scripts and all went well. — Jarek Jóźwik
– Jarek Jóźwik, Commented Dec 13, 2017 at 14:50
@JarekJóźwik After dropping the bands table and creating it again I got an error: ERROR 1215 (HY000): Cannot add foreign key constraint. Using "SHOW ENGINE INNODB STATUS;" I found this: Error in foreign key constraint of table tours/bands: FOREIGN KEY (band_name) REFERENCES musicians(band_name)): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. I'm still not sure what I'm doing wrong — Sean2148
– Sean2148, Commented Dec 13, 2017 at 15:23

O. Jones · Accepted Answer · 2017-12-13 15:35:53Z

2

You need an index on the column referenced by the FK. Your first table definition should be

CREATE TABLE musicians(
    musician_name varchar(30),
    band_name varchar(30),
    PRIMARY KEY(musician_name),
    INDEX (band_name)
);

Pro tip: You must look at error messages when you run SQL statements, especially data-definition statements. Not looking at error messages is like driving with your eyes closed. It works poorly, if at all.

When I ran your data definition statements, I got this.

SQL Error (1005): Can't create table bands (errno: 150 "Foreign key constraint is incorrectly formed")

answered Dec 13, 2017 at 15:35

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sean2148 Over a year ago

Ah, I thought when using a foreign key it creates the index, I didn't realise I had to make one for it too.

Sean2148 Over a year ago

So after some research it looks like you need to be using the InnoDB storange engine for foreign keys that automatically create indexes, yet when I check which storage engine is my default, it says innoDB... strange

Ramesh · Accepted Answer · 2017-12-13 14:53:24Z

I think your foreign key is on the wrong table. Try this

CREATE TABLE bands(
    band_name varchar(30),
    genre varchar(30),
    PRIMARY KEY (band_name)    
);


CREATE TABLE musicians(
    musician_name varchar(30),
    band_name varchar(30),
    PRIMARY KEY(musician_name),
    FOREIGN KEY (band_name) REFERENCES bands(band_name)
);

INSERT INTO bands (band_name, genre) VALUES ('Purple Death', 'Heavy Metal'); 
INSERT INTO bands (band_name, genre) VALUES ('Megapain', 'Heavy Metal'); 
INSERT INTO bands (band_name, genre) VALUES ('Old Strawberry', 'Folk'); 
INSERT INTO bands (band_name, genre) VALUES ('Bakers Dozen', 'Folk'); 
INSERT INTO bands (band_name, genre) VALUES ('Sarabelle Weston', 'Country and Western'); 
INSERT INTO bands (band_name, genre) VALUES ('Dimity Smith', 'Country and Western'); 
INSERT INTO bands (band_name, genre) VALUES ('Old Storm Town', 'Rock'); 
INSERT INTO bands (band_name, genre) VALUES ('Horizons', 'Rock'); 

INSERT INTO musicians (musician_name, band_name) VALUES ('Robinson', 'Purple Death');
INSERT INTO musicians (musician_name, band_name) VALUES ('Harvey', 'Purple Death');
INSERT INTO musicians (musician_name, band_name) VALUES ('Jackson', 'Purple Death');
INSERT INTO musicians (musician_name, band_name) VALUES ('Lynne', 'Megapain');
INSERT INTO musicians (musician_name, band_name) VALUES ('Perry', 'Megapain');
INSERT INTO musicians (musician_name, band_name) VALUES ('Hook', 'Megapain');
INSERT INTO musicians (musician_name, band_name) VALUES ('Matthews', 'Old Strawberry');
INSERT INTO musicians (musician_name, band_name) VALUES ('Lowden', 'Old Strawberry');
INSERT INTO musicians (musician_name, band_name) VALUES ('Bowyer', 'Old Strawberry');
INSERT INTO musicians (musician_name, band_name) VALUES ('Mills', 'Bakers Dozen');
INSERT INTO musicians (musician_name, band_name) VALUES ('Watson', 'Bakers Dozen');
INSERT INTO musicians (musician_name, band_name) VALUES ('Weston', 'Sarabelle Weston');
INSERT INTO musicians (musician_name, band_name) VALUES ('D Smith', 'Dimity Smith');
INSERT INTO musicians (musician_name, band_name) VALUES ('A Smith', 'Dimity Smith');
INSERT INTO musicians (musician_name, band_name) VALUES ('Atkins', 'Old Storm Town');
INSERT INTO musicians (musician_name, band_name) VALUES ('Ambrose', 'Old Storm Town');
INSERT INTO musicians (musician_name, band_name) VALUES ('Jones', 'Old Storm Town');
INSERT INTO musicians (musician_name, band_name) VALUES ('Williams', 'Horizons');
INSERT INTO musicians (musician_name, band_name) VALUES ('Moore', 'Horizons');
INSERT INTO musicians (musician_name, band_name) VALUES ('Tyler', 'Horizons');

I tried that and it just flipped the result, only the bands table would appear

Collectives™ on Stack Overflow

Table not found in mySQL using SQL script

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related