Unable to create table in MySQL

Question

I am unable to create these tables in MySQL. Everything looks complete to me but I keep getting errors.

Here is my SQL:

CREATE TABLE Movies(
   title char PRIMARY KEY NOT NULL, 
   Years date NOT NULL, 
   length decimal not null, 
   genre char NOT NULL, 
   academy_award char not null, 
   FOREIGN KEY(the_name) REFERENCES Studio, 
   FOREIGN KEY(directorName) REFERENCES  Director);

CREATE TABLE StarsIn(
   FOREIGN KEY(title) REFERENCES Movies,  
   FOREIGN KEY(Years)  REFERENCES Movies,
   FOREIGN KEY(the_name) REFERENCES MovieStar);

CREATE TABLE Director(
   the_name char PRIMARY KEY NOT NULL, 
   birthdate date NOT NULL, 
   nationality char NOT NULL);

CREATE TABLE MovieStar(
   the_name char PRIMARY KEY NOT NULL,  
   birthdate date NOT NULL, 
   address char NOT NULL, 
   gender char NOT NULL);

CREATE TABLE Studio(
   the_name char PRIMARY KEY NOT NULL,  
   address char NOT NULL);

You should post the error that you are getting if you want someone to debug your code... — evenro, Oct 28 '14 at 23:30
This is my first time using SQL, have no idea how to create a table — Rohit, Oct 28 '14 at 23:34
IN your foreign keys you are referencing tables which dont exist yet.Start with studio. — Mihai, Oct 28 '14 at 23:46
@Swag you have quite a bit going on here. I've addressed all issues I've seen as well as give an example to a working statement. If you are serious in learning MySQL, I highly recommend [this book](http://www.amazon.com/Murachs-MySQL-Joel-Murach/dp/1890774685). — AdamMc331, Oct 28 '14 at 23:55

evenro · Answer 1 · 2014-10-29T00:26:12.693

The problem you are having is that you are referencing columns in tables that do not exist.

Although you create these tables later, the DBMS engine does not know they will exist.

The solution - create the tables without the FK's that do not exist, and add these later using the "ALTER TABLE" command.

It seems like you know the syntax, so I'll let you inform me if you cannot find it.

your syntax should look like (All I did is re-order, and change char to be archer...):

CREATE TABLE Studio(the_name varchar(50) PRIMARY KEY NOT NULL,  address varchar(50) NOT NULL);
CREATE TABLE Director(the_name varchar(50) PRIMARY KEY NOT NULL, birthdate date NOT NULL, nationality varchar(50) NOT NULL);
CREATE TABLE Movies(title varchar(50) PRIMARY KEY NOT NULL, Years date NOT NULL, 
    length decimal not null, genre varchar(50) NOT NULL, academy_award varchar(50) not null, 
    the_name REFERENCES Studio(the_name), directorname REFERENCES  Director(the_name));
CREATE TABLE MovieStar(the_name char PRIMARY KEY NOT NULL,  birthdate date NOT NULL, address char NOT NULL, gender char NOT NULL);
CREATE TABLE StarsIn(title REFERENCES Movies(title),movie REFERENCES Movies(title), moviestar REFERENCES MovieStar(the_name));

A little note, depends on the version - you'd might have to use the FOREIGN KEY(col_name) instead of col_name. I like more the syntax without it, but some versions force you to use FOREIGN KEY(title) instead of title in the last SQL for example.

The syntax you used - Foreign key NAME References TABLE (Column) -- you forgot the column-- Allows you to name the FK. If you don't use the "Foreign Key" the system will randomly assign a name. This is usually not important, unless you want the DB design to be "clean" and to be able to reference it by name.

P.S. - I did not run it, I trust you to inform if there's any other issue - I did not check the syntax, just fixed the error you reported- references that do not exist... P.S. P.S. Always check syntax... http://dev.mysql.com/doc/refman/5.1/en/create-table.html

I tried putting the tables in order but there seems to be an error with any line that includes the FOREIGN Key statement. I don`t know to use the ALTER syntax with the Foreign Keys. — Rohit, Oct 28 '14 at 23:45
Fixed the syntax of the reference now.. added a link to the syntax book ... I am an Oracle person, each DBMS have a bit different syntax- always check the manual for these... — evenro, Oct 28 '14 at 23:48
Did it work? or more syntax errors? note the difference between varchar to char, and change it to the appropriate max length... — evenro, Oct 28 '14 at 23:55
@McAdam331 - the syntax is correct - the only thing was pointing to wrong columns... — evenro, Oct 29 '14 at 00:21
@swag - I ran the current syntax and it works.. so if you have any other issues - let me know .. note you cannot reference to year .. you need to reference the primary key (or a column that has a unique index on it..) — evenro, Oct 29 '14 at 00:22

AdamMc331 · Answer 2 · 2014-10-29T00:01:22.887

This isn't working because your tables are not being created in the proper order.

The parent table must always be created first, because otherwise you will be trying to reference a column that does not exist.

For example, look at this part here:

CREATE TABLE Movies(title char PRIMARY KEY NOT NULL, Years date NOT NULL, 
length decimal not null, genre char NOT NULL, academy_award char not null, 
FOREIGN KEY(the_name) REFERENCES Studio, FOREIGN KEY(directorName) REFERENCES  Director);

You can't reference tables studio or director because they don't even exist.

The proper order could be:

Studio
Movie Star
Director
Movies
StarsIn

In addition, you have a lot of other problems going on here.

You do not define any columns in the Stars IN table, you only declare foreign keys:

CREATE TABLE StarsIn(
FOREIGN KEY(title) REFERENCES Movies,  
FOREIGN KEY(Years)  REFERENCES Movies,
FOREIGN KEY(the_name) REFERENCES MovieStar);

Not only are you not defining any columns, you aren't referencing anything. This will also throw an error. Your foreign key title must reference some column in the movies table. And in addition to that, you can't create a foreign key reference for Years. Mostly because you cannot create a foreign key to a column that is not primary key in another table, and also why should you need two foreign keys to the same table?

Those problems exist in other create table statements as well, so given everything I've said please go back and look at each of your create table statements.

Here is a working SQL Fiddle you can use for reference though.

EDIT

I would like to also add that I do not recommend using the char datatype. Please look into using VARCHAR().

Unable to create table in MySQL

2 Answers2