Syntax Error for Create Table in mySQL

Question

I am trying to create a new table in mySQL, and it shows me this error:

A closing bracket was expected (near )).

Referring to the closing bracket followed by the semi-colon. I think it has something to do with the CONSTRAINTS line as it goes away when I remove them.

CREATE TABLE 'User' (
'FName' varchar(10),
'LName' varchar(10),
'Email' varchar(20), 
'Phone' int(20), 
'Password' varchar(20),
'Address' varchar(100),
'BuyerNum' int(3),
'SellerNum' int(3),
  CONSTRAINT 'User_FName_nn' NOT NULL('FName')
  CONSTRAINT 'User_LName_nn' NOT NULL('LName'),
  CONSTRAINT 'User_Email_pk' PRIMARY KEY('Email'),
  CONSTRAINT 'User_Phone_uk' UNIQUE('Phone'),
  CONSTRAINT 'User_Pass_nn' NOT NULL('Pass')
);

https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql — fancyPants, Feb 28 '18 at 11:27
You have a missing comma after this line: `CONSTRAINT 'User_FName_nn' NOT NULL('FName')`. You should also use backticks, not single quotes. — BenM, Feb 28 '18 at 11:28

Topher · Accepted Answer · 2018-08-06T08:09:57.610

You're missing a comma after this line: CONSTRAINT 'User_FName_nn' NOT NULL('FName')

CREATE TABLE 'User' (
'FName' varchar(10),
'LName' varchar(10),
'Email' varchar(20), 
'Phone' int(20), 
'Password' varchar(20),
'Address' varchar(100),
'BuyerNum' int(3),
'SellerNum' int(3),
  CONSTRAINT 'User_FName_nn' NOT NULL('FName')    <--- ,
  CONSTRAINT 'User_LName_nn' NOT NULL('LName'),
  CONSTRAINT 'User_Email_pk' PRIMARY KEY('Email'),
  CONSTRAINT 'User_Phone_uk' UNIQUE('Phone'),
  CONSTRAINT 'User_Pass_nn' NOT NULL('Pass')
);

And as others have said:

You should use back ticks (`) instead of single quotes (').
Moving the constraints into the definition will make it more readable and less confusing.
You should have a UserId auto_increment column as the primary key instead of using the email column.

Gordon Linoff's answer provides a few nice tips that you should definitely look into and understand why you want to use them as you go forward designing the rest of your database.

Edit: An alternate solution would be as follows:

CREATE TABLE User2 (
UserId int auto_increment PRIMARY KEY,
FName varchar(10) NOT NULL,
LName varchar(10),
Email varchar(20) NOT NULL UNIQUE, 
Phone int(20), 
Password varchar(20)NOT NULL,
Address varchar(100),
BuyerNum int(3),
SellerNum int(3)
);

This will still cause issues because of the single quotes. – BenM Feb 28 '18 at 11:34 — BenM, Feb 28 '18 at 11:34
Must use backtick \` not single quote. – VISQL Aug 05 '18 at 02:40 — VISQL, Aug 05 '18 at 02:40

score 1 · Answer 2 · answered Feb 28 '18 at 11:34

You have a few issues here, and I don't think the error does disappear when you remove the CONSTRAINT clauses. You're using single quotes (') instead of backticks (`).

You are also missing a comma after your first CONSTRAINT clause. Update as follows:

CREATE TABLE `User` (
`FName` varchar(10) NOT NULL,
`LName` varchar(10) NOT NULL,
`Email` varchar(20), 
`Phone` int(20), 
`Password` varchar(20) NOT NULL,
`Address` varchar(100),
`BuyerNum` int(3),
`SellerNum` int(3),
  CONSTRAINT `User_Email_pk` PRIMARY KEY(`Email`),
  CONSTRAINT `User_Phone_uk` UNIQUE(`Phone`)
);

Notice that we also removed the CONSTRAINT clauses for NOT NULL, and rather added them to the column definition.

score 0 · Answer 3 · answered Feb 28 '18 at 11:28

0

You need to put a comma after the end of your first CONSTRAINT line.

answered Feb 28 '18 at 11:28

Adi219

4,712
2
20
43

score 0 · Answer 4 · answered Feb 28 '18 at 11:31

0

You Forgot , after 
CONSTRAINT 'User_FName_nn' NOT NULL('FName') this

answered Feb 28 '18 at 11:31

Mohini

268
3
15

score 0 · Answer 5 · answered Feb 28 '18 at 11:31

Remove single quote and add NOT NULL to the desired fields.

And, if possible do not use reserved words.

CREATE TABLE User_Account 
(
FName varchar(10) NOT NULL,
LName varchar(10) NOT NULL,
Email varchar(20), 
Phone int(20), 
Password varchar(20) NOT NULL,
Address varchar(100),
BuyerNum int(3),
SellerNum int(3),
  CONSTRAINT User_Email_pk PRIMARY KEY(Email),
  CONSTRAINT User_Phone_uk UNIQUE(Phone)
);

✓

dbfiddle here

Gordon Linoff · Answer 6 · 2018-02-28T11:39:58.707

First, I assume that the single quotes are all typos in the question.

Second, put these short constraints in the column definitions, unless you have a really strong urge to name them:

CREATE TABLE User (
    FName varchar(10) NOT NULL,
    LName varchar(10) NOT NULL,
    Email varchar(20) PRIMARY KEY, 
    Phone varchar(20) UNIQUE, 
    Password varchar(20) NOT NULL,  -- should be encrypted
    Address varchar(100),
    BuyerNum int(3),
    SellerNum int(3)
);

When you split the definition, it makes it harder to figure out how a given column is really defined. "Oh, it is a string that accepts NULL values. Wait! It is also NOT NULL. Wait! It is also UNIQUE."

I also changed the phone to a string. Some numbers begin with "0"s or "+", for instance.

Also, you should have a userId as the first column:

CREATE TABLE User (
    UserId int auto_increment PRIMARY KEY,
    FName varchar(10) NOT NULL,
    LName varchar(10) NOT NULL,
    Email varchar(20) NOT NULL UNIQUE, 
    Phone varchar(20) UNIQUE, 
    Password varchar(20) NOT NULL,  -- should be encrypted
    Address varchar(100),
    BuyerNum int(3),
    SellerNum int(3)
);

Why?

Your structure does not allow a user to change their email (because as the primary key it is referenced in other tables).
Strings are less efficient in foreign key indexes.

score 0 · Answer 7 · answered Feb 28 '18 at 11:35

0

just put a (comma) , after every CONSTRAINT except the last CONSTRAINT. such that: CONSTRAINT 'User_FName_nn' NOT NULL('FName'),

answered Feb 28 '18 at 11:35

Tasnuva Leeya

2,515
1
13
21

score 0 · Answer 8 · answered Feb 28 '18 at 11:36

Table name does not need single quotes, and u are missing an comma as suggested by everyone else after first line of constraints.

Here... CREATE TABLE user ( FName varchar(10) NOT NULL, LName varchar(10) NOT NULL, Email varchar(20), Phone int(20), Password varchar(20) NOT NULL, Address varchar(100), BuyerNum int(3), SellerNum int(3), CONSTRAINT User_Email_pk PRIMARY KEY(Email), CONSTRAINT User_Phone_uk UNIQUE(Phone) );

Syntax Error for Create Table in mySQL

8 Answers8