I can't create a table in MySQL, Please give a solution

Question

My code :

CREATE DATABASE IF NOT EXISTS thogakade;

USE thogakade;

CREATE TABLE customer(
    customerId VARCHAR(10),
    name VARCHAR(50),
    address TEXT,
    salary DECIMAL(10,2),
    CONSTRAINT PRIMARY KEY(customerId)
    );

CREATE TABLE order(
    orderId VARCHAR(30),
    orderDate DATE NOT NULL,
    custId VARCHAR(45),
    CONSTRAINT PRIMARY KEY(orderId),
    CONSTRAINT FOREIGN KEY(custId) REFERENCES customer(customerId)
    );

This is my code and I can't create a order table and I got this error message,

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 'order(
orderId VARCHAR(30),
orderDate DATE NOT NULL,
custId VARCHAR(45),
CONSTRA' at line 1

Please give me a solution for this.

Does this answer your question? [Syntax error due to using a reserved word as a table or column name in MySQL](https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — Luuk, Sep 30 '22 at 07:41

score 1 · Accepted Answer · answered Sep 30 '22 at 07:37

order is a reserved keyword in mysql,you need to use ` to wrap it when you want to use it as table name or column name

CREATE TABLE `order`(
  orderId VARCHAR(30),
  orderDate DATE NOT NULL,
  custId VARCHAR(45),
  CONSTRAINT PRIMARY KEY(orderId),
  CONSTRAINT FOREIGN KEY(custId) REFERENCES customer(customerId)
);

Also,it would be good to let customerId and custId have the same type and lenght if you want to add foreign key reference.

DB Fiddle Demo

score -1 · Answer 2 · answered Sep 30 '22 at 07:47

Try it like this

CREATE TABLE order(
orderId VARCHAR(30),
orderDate DATE NOT NULL,
custId INT,
PRIMARY KEY(orderId),
FOREIGN KEY(custId) REFERENCES customer(customerId)
);

Note your customer.customerId should be of same datatype and length as order.custId

I can't create a table in MySQL, Please give a solution

2 Answers2