CODEWITHSUNDEEP
mysqlsqldatabasesyntax
Malak Sadek
Malak Sadek

Reputation: 35

Syntax Error for Create Table in mySQL

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')
);

Upvotes: 1

Views: 3990

Answers (8)

Topher
Topher

Reputation: 1029

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)
);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 0

niranjan_harpale
niranjan_harpale

Reputation: 2264

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) );

Upvotes: 0

Tasnuva Leeya
Tasnuva Leeya

Reputation: 2795

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

Upvotes: 0

BenM
BenM

Reputation: 53198

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.

Upvotes: 1

McNets
McNets

Reputation: 10807

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

Upvotes: 0

Mohini
Mohini

Reputation: 268

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

Upvotes: 0

Adi219
Adi219

Reputation: 4814

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

Upvotes: 0

Related Questions