CODEWITHSUNDEEP
mysqlsql
POP86
POP86

Reputation: 111

What's wrong with my query

Following is my query:

CREATE TABLE report_invoice(
'ID' INT(10) NOT NULL AUTO_INCREMENT,
'Invoice_No' VARCHAR(30) NOT NULL,
'Invoice_Type' INT(10) NOT NULL,
'Operator' VARCHAR(50) NOT NULL,
'Customer' VARCHAR(50) NOT NULL,
'Invoice_Date' DATE NOT NULL,
'Total' DECIMAL('10,2'),
PRIMARY KEY ('ID'));

I keep getting 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 ''ID'
INT(10) NOT NULL AUTO_INCREMENT,
'Invoice_No' VARCHAR(30) NOT NULL,
'Invoic' at line 2

Upvotes: 1

Views: 102

Answers (4)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Don't use simple quotes

You may replace them with backticks, or suppress them.

They would be usefull if you want to use reserved keywords as column names (bad idea anyway), or completely numeric column names, or special characters in column names.

So not in your case.

Don't put quotes around decimal scale and precision, too.

So this would do the job.

CREATE TABLE report_invoice(
ID INT(10) NOT NULL AUTO_INCREMENT,
Invoice_No VARCHAR(30) NOT NULL,
Invoice_Type INT(10) NOT NULL,
Operator VARCHAR(50) NOT NULL,
Customer VARCHAR(50) NOT NULL,
Invoice_Date DATE NOT NULL,
Total DECIMAL(10,2),
PRIMARY KEY (ID));

Upvotes: 3

MHOOS
MHOOS

Reputation: 5306

CREATE TABLE report_invoice(
Id INT IDENTITY PRIMARY KEY,
Invoice_No VARCHAR(30) NOT NULL,
Invoice_Type INT NOT NULL,
Operator VARCHAR(50) NOT NULL,
Customer VARCHAR(50) NOT NULL,
Invoice_Date DATE NOT NULL,
Total DECIMAL );

Upvotes: 0

Alexandre Santos
Alexandre Santos

Reputation: 8338

Remove the ticks or replace them with back ticks. Same for numbers.

CREATE TABLE report_invoice( ID INT(10) NOT NULL AUTO_INCREMENT, Invoice_No VARCHAR(30) NOT NULL, Invoice_Type INT(10) NOT NULL, Operator VARCHAR(50) NOT NULL, Customer VARCHAR(50) NOT NULL, Invoice_Date DATE NOT NULL, Total DECIMAL(10,2), PRIMARY KEY (ID));

Upvotes: 0

scragar
scragar

Reputation: 6824

You're using single quotes around your field names, use backticks instead:

CREATE TABLE report_invoice(
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`Invoice_No` VARCHAR(30) NOT NULL,
`Invoice_Type` INT(10) NOT NULL,
`Operator` VARCHAR(50) NOT NULL,
`Customer` VARCHAR(50) NOT NULL,
`Invoice_Date` DATE NOT NULL,
`Total` DECIMAL(10,2),
PRIMARY KEY (`ID`));

Upvotes: 4

Related Questions