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

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.

CodePudding user response：

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

CodePudding user response：

You need to specify names for your keys

https://www.w3schools.com/sql/sql_primarykey.ASP

Try this :

CREATE DATABASE IF NOT EXISTS thogakade;

USE thogakade;

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

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

CodePudding user response：

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