MySQL: "CREATE DATABASE" results in MySQL Error Code: 1064

Question

I recently bought this book called "SQL Queries for Mere Mortals (3rd Edition" to study SQL. It came with MySQL scripts that they said I could run and have example databases to work with and follow along with the book. However, some of the scripts are resulting in an error message. Here is one example script that will not work:

CREATE DATABASE EntertainmentAgencyModify;

USE EntertainmentAgencyModify;

CREATE TABLE Agents (
    AgentID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    AgtFirstName nvarchar (25) NULL ,
    AgtLastName nvarchar (25) NULL ,
    AgtStreetAddress nvarchar (50) NULL ,
    AgtCity nvarchar (30) NULL ,
    AgtState nvarchar (2) NULL ,
    AgtZipCode nvarchar (10) NULL ,
    AgtPhoneNumber nvarchar (15) NULL ,
    DateHired date NULL ,
    Salary decimal(15, 2) NULL DEFAULT 0 ,
    CommissionRate float(24) NULL 
);

CREATE TABLE Customers (
    CustomerID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    CustFirstName nvarchar (25) NULL ,
    CustLastName nvarchar (25) NULL ,
    CustStreetAddress nvarchar (50) NULL ,
    CustCity nvarchar (30) NULL ,
    CustState nvarchar (2) NULL ,
    CustZipCode nvarchar (10) NULL ,
    CustPhoneNumber nvarchar (15) NULL
);

CREATE TABLE Engagements (
    EngagementNumber int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StartDate date NULL ,
    EndDate date NULL ,
    StartTime time NULL ,
    StopTime time NULL ,
    ContractPrice decimal(15,2) NULL DEFAULT 0 ,
    CustomerID int NULL DEFAULT 0 ,
    AgentID int NULL DEFAULT 0 ,
    EntertainerID int NULL DEFAULT 0 
);

CREATE TABLE Engagements_Archive (
    EngagementNumber int NOT NULL ,
    StartDate date NULL ,
    EndDate date NULL ,
    StartTime time NULL ,
    StopTime time NULL ,
    ContractPrice decimal(15,2) NULL ,
    CustomerID int NULL ,
    AgentID int NULL ,
    EntertainerID int NULL 
);

CREATE TABLE Entertainer_Members (
    EntertainerID int NOT NULL ,
    MemberID int NOT NULL DEFAULT 0 ,
    Status smallint NULL DEFAULT 0 
);

CREATE TABLE Entertainer_Styles (
    EntertainerID int NOT NULL ,
    StyleID int NOT NULL DEFAULT 0 
);

CREATE TABLE Entertainers (
    EntertainerID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    EntStageName nvarchar (50) NULL ,
    EntSSN nvarchar (12) NULL ,
    EntStreetAddress nvarchar (50) NULL ,
    EntCity nvarchar (30) NULL ,
    EntState nvarchar (2) NULL ,
    EntZipCode nvarchar (10) NULL ,
    EntPhoneNumber nvarchar (15) NULL ,
    EntWebPage nvarchar (50) NULL ,
    EntEMailAddress nvarchar (50) NULL ,
    DateEntered date NULL ,
    EntPricePerDay decimal(15,2) NULL 
);

CREATE TABLE Members (
    MemberID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    MbrFirstName nvarchar (25) NULL ,
    MbrLastName nvarchar (25) NULL ,
    MbrPhoneNumber nvarchar (15) NULL ,
    Gender nvarchar (2) NULL 
);

CREATE TABLE Musical_Preferences (
    CustomerID int NOT NULL DEFAULT 0 ,
    StyleID int NOT NULL DEFAULT 0 
);

CREATE TABLE Musical_Styles (
    StyleID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StyleName nvarchar (75) NULL 
);

CREATE  INDEX AgtZipCode ON Agents(AgtZipCode);

CREATE  INDEX CustZipCode ON Customers(CustZipCode);

CREATE  INDEX AgentsEngagements ON Engagements(AgentID);

CREATE  INDEX CustomerID ON Engagements(CustomerID);

CREATE  INDEX EmployeeID ON Engagements(AgentID);

CREATE  INDEX EntertainerID ON Engagements(EntertainerID);

ALTER TABLE Engagements_Archive 
    ADD CONSTRAINT Engagements_Archive_PK PRIMARY KEY 
    (
        EngagementNumber
    );

CREATE  INDEX CustomerID ON Engagements_Archive(CustomerID);

CREATE  INDEX EmployeeID ON Engagements_Archive(AgentID);

CREATE  INDEX EntertainerID ON Engagements_Archive(EntertainerID);

ALTER TABLE Entertainer_Members 
    ADD CONSTRAINT Entertainer_Members_PK PRIMARY KEY 
    (
            EntertainerID, 
            MemberID
    );

CREATE  INDEX EntertainersEntertainer_Members ON Entertainer_Members(EntertainerID);

CREATE  INDEX MembersEntertainer_Members ON Entertainer_Members(MemberID);

ALTER TABLE Entertainer_Styles 
    ADD CONSTRAINT Entertainer_Styles_PK PRIMARY KEY 
    (
            EntertainerID, 
            StyleID
    );

CREATE  INDEX EntertainersEntertainer_Styles ON Entertainer_Styles(EntertainerID);

CREATE  INDEX Musical_StylesEntertainer_Styles ON Entertainer_Styles(StyleID);

CREATE  INDEX EntZipCode ON Entertainers(EntZipCode);

ALTER TABLE Musical_Preferences 
    ADD CONSTRAINT Musical_Preferences_PK PRIMARY KEY 
    (
            CustomerID, 
            StyleID
    );

CREATE  INDEX CustomerID ON Musical_Preferences(CustomerID);

CREATE  INDEX StyleID ON Musical_Preferences(StyleID);

ALTER TABLE Engagements 
    ADD CONSTRAINT Engagements_FK00 FOREIGN KEY 
    (
            AgentID
    ) REFERENCES Agents (
            AgentID
    ),
    ADD CONSTRAINT Engagements_FK01 FOREIGN KEY 
    (
            CustomerID
    ) REFERENCES Customers (
            CustomerID
    ),
    ADD CONSTRAINT Engagements_FK02 FOREIGN KEY 
    (
           EntertainerID
    ) REFERENCES Entertainers (
           EntertainerID
    );

ALTER TABLE Entertainer_Members 
    ADD CONSTRAINT Entertainer_Members_FK00 FOREIGN KEY 
    (
            EntertainerID
    ) REFERENCES Entertainers (
            EntertainerID
    ),
    ADD CONSTRAINT Entertainer_Members_FK01 FOREIGN KEY 
    (
            MemberID
    ) REFERENCES Members (
            MemberID
    );

ALTER TABLE Entertainer_Styles 
    ADD CONSTRAINT Entertainer_Styles_FK00 FOREIGN KEY 
    (
            EntertainerID
    ) REFERENCES Entertainers (
            EntertainerID
    ) ON DELETE CASCADE,
    ADD CONSTRAINT Entertainer_Styles_FK01 FOREIGN KEY 
    (
            StyleID
    ) REFERENCES Musical_Styles 
    (
            StyleID
    );

ALTER TABLE Musical_Preferences 
    ADD CONSTRAINT Musical_Preferences_FK00 FOREIGN KEY 
    (
            CustomerID
    ) REFERENCES Customers (
            CustomerID
    ) ON DELETE CASCADE,
    ADD CONSTRAINT Musical_Preferences_FK01 FOREIGN KEY 
    (
            StyleID
    ) REFERENCES Musical_Styles (
            StyleID
    );

Running this script results in the following error message:

16:33:35 CREATE DATABASE EntertainmentAgencyModify Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DATABASE EntertainmentAgencyModify' at line 1 0.00041 sec

Screenshot

I have tried running other scripts that came with the book that also starts with "CREATE DATABASE" and some of them ran smoothly without any errors, so I'm confused as to why I'm getting this error message. Any help would be greatly appreciated! Thanks all!

Aren't you a bit too lazy now? If you can't figure out whats wrong, remove stuff from the end until the error disappears. Once you've identified the problem, it's easier to fix it! — jarlh
– jarlh, Commented Jul 6, 2016 at 7:54
@jarlh Come on, the error message says it's in the first line. — MAP
– MAP, Commented Jul 6, 2016 at 8:04
@jarlh I didn't say that (I may have thought it, but it didn't escape through my fingers into the keyboard :-). — MAP
– MAP, Commented Jul 6, 2016 at 8:12
@Joe - Did you try to run them one by one and find out which failed? — Gilad Green
– Gilad Green, Commented Jul 6, 2016 at 8:45

MAP · Accepted Answer · 2016-07-06 08:02:42Z

1

Note that since this script has a "CREATE DATABASE" at the top it will fail if that database already exists. That also means that if it works partially and you run it again, it;ll fail completely the next time. "CREATE DATABASE" is a pretty big hammer, so you probably don't want to get into the habit of doing it a lot. But, having said that, here's an even bigger hammer; add

DROP DATABASE IF EXISTS EntertainmentAgencyModify;

in front of the CREATE and similarly for the others. When you have real data, you, of course, never want to use either of these.

answered Jul 6, 2016 at 8:02

MAP

9027 silver badges11 bronze badges

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

5 Comments

Frank van Luijn Over a year ago

But if the database exists, that wouldnt result in a syntax error, would it?

MAP Over a year ago

I've never been completely confident about what types of error messages mysql produces for various realities. The message says it's in the first line. It looks OK to me except for this possible case, so I mentioned it. I would have just put a comment, but at the time I couldn't comment (but now I can :-).

Frank van Luijn Over a year ago

I hear that, try creating a table named 'order' but without quoting the name, a nice syntax error pops up instead of wrong usage of reserved word - technically correct but still maddeningly frustrating if you miss it :)

Elzo Valugi Over a year ago

if database exists there is another error message: Error Code: 1007. Can't create database 'EntertainmentAgencyModify'; database exists

Joe Over a year ago

Thanks @MAP I had checked for that possibility by making sure that the database didn't exist. That didn't work for this particular problem I had. Very valid point though!

Frank van Luijn · Accepted Answer · 2016-07-06 09:20:37Z

0

Can you try running the following just by typing it in your mysql console:

CREATE DATABASE EntertainmentAgencyModify;

It should simply work, or at least tell you something more then a syntax error.

edited Jul 6, 2016 at 9:20

answered Jul 6, 2016 at 9:05

Frank van Luijn

4704 silver badges16 bronze badges

4 Comments

Gilad Green Over a year ago

It doesn't need the quotes

Frank van Luijn Over a year ago

It seems it doesnt even work with quotes xD i should check things before posting an answer

Frank van Luijn Over a year ago

@gilad updated my answer, thanks for the heads up :)

Joe Over a year ago

@FrankvanLuijn Thank you very much for this suggestion! This is what tipped me off something was weird. I tried deleting the rest of the script and leaving only the first line and running it, as you suggested. It still didn't work. So I opened up a new editor and typed the exact same command, letter by letter, then it worked. Weird.

Aseem PM · Accepted Answer · 2016-07-06 10:44:24Z

0

The code executes fine. May be its a problem with your MySql.

Try to install a new MySql. :)

https://www.mysql.com/downloads/

answered Jul 6, 2016 at 10:44

Aseem PM

956 bronze badges

1 Comment

Joe Over a year ago

Thanks @Aseem I think I have the latest versions (5.7.13 for community server and 6.37.7 for workbench). Solved the issue though. Thanks for your help.

Joe · Accepted Answer · 2016-07-07 09:08:07Z

0

Thanks everyone! I have no idea why this works, but I found a solution. I had to simply delete "CREATE " from the first line of the script and re-type it. Then it worked. If anyone has any idea why this seemingly irrelevant solution worked, I'd love to know. Thanks everyone for your help!

answered Jul 7, 2016 at 9:08

Joe

233 bronze badges

1 Comment

Frank van Luijn Over a year ago

Maybe when you copy pasted the script an undisplayable character got copied too, typing it by hand removed the character mysql was hanging on. Glad it works!

Collectives™ on Stack Overflow

MySQL: "CREATE DATABASE" results in MySQL Error Code: 1064

4 Answers 4

5 Comments

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest