Syntax error 1064 in CREATE TABLE statement with TINYTEXT columns?

Question

This is the MySQL code I have so far:

CREATE DATABASE bankbase;

USE bankbase;

CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0,
ClientFirstName VARCHAR(30) NOT NULL DEFAULT "first name",
ClientLastName VARCHAR(30) NOT NULL DEFAULT "last name",
ClientPhone CHAR(10) NOT NULL, ClientEmail 

TINYTEXT(30) NULL,
ClientAddress TINYTEXT(128) NOT NULL,
PRIMARYKEY(ClientID)
);

CREATE TABLE branchtable(
BranchID SMALLINT(15) NOT NULL DEFAULT 0,
BranchCity TINYTEXT(30) NOT NULL DEFAULT city, 

BranchManagerFName VARCHAR(30) NULL DEFAULT "Branch Manager's First Name",
BranchManagerLName VARCHAR(30) NULL 

DEFAULT "Branch Manager's LAst Name",
BranchPhone CHAR(10) NOT NULL,
BranchEmail TINYTEXT(30) NULL DEFAULT @bank.com,
PRIMARYKEY(BranchID)
);

CREATE TABLE transactiontable(
TypeID SMALLINT(15) NOT NULL DEFAULT 0,
Type ENUM('CHEQUING','SAVINGS') NOT NULL,
TransAmount DECIMAL NOT NULL,
TransDate TIMESTAMP NOT NULL,
Balance DOUBLE NOT NULL,
PRIMARYKEY(TypeID)
);

Running this code in MySQL on the command line gives me this error:

ERROR 1064 (42000): 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 '(TypeID) )' at line 7

I've tried creating different test tables, leaving out the NULL/NOT NULL, but nothing seems to work. Any help would be very, very appreciated!

keep it simple. Try to get the simplest, smallest table to work first. Also if this is a script, do any of these DDLs work by themself if you paste in and execute? If so, start from there, and add table creations until you see which one is causing the problem. (maybe more than one). Finally, you may have successfully created a table, and now the error messages refers to an error like "can't make table, it already exists", so include DROP Table X before creating it. Good luck. — shellter, Jun 27 '12 at 02:49
Please post the full error. You cut it off right where we need to see it _right syntax to use near_??? — Michael Berkowski, Jun 27 '12 at 02:50
Looks to be missing some quotes in the default `BranchEmail TINYTEXT(30) NULL DEFAULT @bank.com,` — Michael Berkowski, Jun 27 '12 at 02:51
@Michael There are numerous problems with this SQL code. See my answer for the full details, but among other items, `TINYTEXT` columns cannot be assigned default values, according to the documentation. — Ricardo Altamirano, Jun 27 '12 at 11:30
@doodlesalot If the answer was helpful, click the checkmark underneath the vote total by the answer to accept it so other people can benefit. — Ricardo Altamirano, Jul 10 '12 at 14:18

score 10 · Answer 1 · edited Jun 20 '20 at 09:12

Here are the problems I see with your original script:

BLOB and TEXT columns cannot have DEFAULT values.
TINYTEXT is the same as VARCHAR(255), so you can't declare a maximum length for a TINYTEXT field because one is already implied.
You need a space between the words PRIMARYKEY. It should be PRIMARY KEY.
Finally, it isn't a problem per se, but in your first CREATE TABLE statement, you have odd spacing. Changing the ClientEmail line to the following makes it a lot more readable:

Much better:

ClientPhone CHAR(10) NOT NULL,
ClientEmail TINYTEXT NOT NULL,

See the MySQL documentation for more information. After all of those corrections, these are the working MySQL queries:

CREATE DATABASE bankbase;
USE bankbase;
    
CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0,
ClientFirstName VARCHAR(30) NOT NULL DEFAULT "first name",
ClientLastName VARCHAR(30) NOT NULL DEFAULT "last name",
ClientPhone CHAR(10) NOT NULL,
ClientEmail TINYTEXT NULL,
ClientAddress TINYTEXT NOT NULL,
PRIMARY KEY(ClientID)
);

CREATE TABLE branchtable(
BranchID SMALLINT(15) NOT NULL DEFAULT 0,
BranchCity TINYTEXT NOT NULL,
BranchManagerFName VARCHAR(30) NULL DEFAULT "Branch Manager's First Name", 
BranchManagerLName VARCHAR(30) NULL DEFAULT "Branch Manager's LAst Name",
BranchPhone CHAR(10) NOT NULL,
BranchEmail TINYTEXT NULL,
PRIMARY KEY(BranchID)
);
    
CREATE TABLE transactiontable(
TypeID SMALLINT(15) NOT NULL DEFAULT 0,
Type ENUM('CHEQUING','SAVINGS') NOT NULL,
TransAmount DECIMAL NOT NULL,
TransDate TIMESTAMP NOT NULL,
Balance DOUBLE NOT NULL,
PRIMARY KEY(TypeID)
);

If you click Build Schema in this SQL fiddle, you'll see that it works!

A bit more information on how to methodically fix errors like this

If you're methodical, these problems are easy to solve, especially with CREATE TABLE statements. For example, when you're debugging the first CREATE TABLE statement, move through the column declarations one at at time.

Try making a table with just the first column:

CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0);

That code works so delete the table and add columns one by one until you add one that throws an error:

DROP TABLE clienttable;
CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0,
ClientFirstName VARCHAR(30) NOT NULL DEFAULT "first name",
ClientLastName VARCHAR(30) NOT NULL DEFAULT "last name",
ClientPhone CHAR(10) NOT NULL, ClientEmail 

TINYTEXT(30) NULL);

We get the error you asked about:

ERROR 1064 (42000): 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 '(30) NULL)' at line 7

Now you know exactly which line has the error. I've even seen code written like this occasionally (this is the same code as in the previous example):

CREATE TABLE clienttable(
ClientID
SMALLINT(15) 
NOT NULL 
DEFAULT 0,
ClientFirstName 
VARCHAR(30) 
NOT NULL 
DEFAULT "first name",
ClientLastName 
VARCHAR(30) 
NOT NULL 
DEFAULT "last name",
ClientPhone 
CHAR(10) 
NOT NULL,
ClientEmail 
TINYTEXT(30) //Line 18 <- This is where the error occurs 
NULL);

Yes, it's not highly readable, but if we run it, we get a syntax error at line 18, i.e. the TINYTEXT(30) line. Reading the documentation, asking online, etc. would show you wy this is wrong. Once all the errors are fixed, make the code readable again and you're set.

Thank you!! So, if I understand correctly, my major errors were: forgetting to hit enter in the first table, and trying to give TINYTEXT a max length? — doodlesalot, Jun 27 '12 at 03:13
@doodlesalot The majors errors were a) Trying to give TINYTEXT a max length, b) trying to assign a default value to a TEXT/TINYTEXT type column, and c) not using the correct keywords (e.g. PRIMARYKEY instead of PRIMARY KEY). I don't know what you mean by "failing to hit enter." — Ricardo Altamirano, Jul 10 '12 at 14:18

Syntax error 1064 in CREATE TABLE statement with TINYTEXT columns?

1 Answers1

A bit more information on how to methodically fix errors like this

Linked