CODEWITHSUNDEEP
mysqlsqldatabase
user2319199
user2319199

Reputation: 21

SQL beginner error creating a table

So im new to sql and im trying to create a series of tables for cant seem to get it to work. The error message that I get is. The tables all add in individually no problem but when I want to import all of them at once it gives me this error.

Schema Creation Failed: 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 '[Customer]( CustomerID int PRIMARY KEY, CustomerName varchar(' at line 1: 

CREATE TABLE IF NOT EXISTS  [Customer](
      CustomerID     int  PRIMARY KEY,
      CustomerName   varchar(50)  NOT NULL,
      BillingAddress varchar(400)  default NULL,
      PhoneNumber    int  default NULL,
      User_Email     varchar(128) UNIQUE
 )ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS [Order](
      OrderID             int NOT NULL  PRIMARY KEY,
      CustomerID          int,
      OrderDate           date  NOT NULL,
      PurchaseOrderNumber int
 )ENGINE=InnoDB DEFAULT CHARSET=latin1;





CREATE TABLE IF NOT EXISTS [OrderItem](
      OrderItemID    int  PRIMARY KEY,
      ProductID      int NOT NULL,
      OrderID        int NOT NULL,
      Quantity       int NOT NULL,
      PricePerUnit   double NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;




CREATE TABLE IF NOT EXISTS [Product](
       ProductID            int PRIMARY KEY,
       ProductName          varchar(100)  NOT NULL,
       ProductDescription   varchar(100)  NOT NULL,
       SerialNumber         int NOT NULL,
       UnitesInStock        int  NOT NULL,
       UnitsOnOrder         int NOT NULL,
       UnitPrice            double NOT NULL
       SupplierID           int not NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;




CREATE TABLE IF NOT EXISTS [SupplySchedule](
       ShippingID        int PRIMARY KEY,
       ShippingMode      varchar(50) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=latin1;







CREATE TABLE IF NOT EXISTS [Supplier](
        SupplierID       int PRIMARY KEY,
        SupplierName     varchar(40) NOT NULL,
        SupplierAddress  varchar(128) NOT NULL,
        ContactName      varchar(128) NOT NULL


   )ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 0

Views: 91

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 1549

I have rename Order table to OrderMaster as "Order" is mysql reserved word try below script:

drop table if exists Customer;
CREATE TABLE   Customer(
      CustomerID     int  PRIMARY KEY,
      CustomerName   varchar(50)  NOT NULL,
      BillingAddress varchar(400)  default NULL,
      PhoneNumber    int  default NULL,
      User_Email     varchar(128) UNIQUE
 )ENGINE=InnoDB DEFAULT CHARSET=latin1;

drop table if exists OrderMaster;
CREATE TABLE IF NOT EXISTS OrderMaster(
      OrderID             int NOT NULL  PRIMARY KEY,
      CustomerID          int,
      OrderDate           date  NOT NULL,
      PurchaseOrderNumber int
 )ENGINE=InnoDB DEFAULT CHARSET=latin1;




drop table if exists OrderItem;
CREATE TABLE OrderItem(
      OrderItemID    int  PRIMARY KEY,
      ProductID      int NOT NULL,
      OrderID        int NOT NULL,
      Quantity       int NOT NULL,
      PricePerUnit   double NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;



drop table if exists Product;
CREATE TABLE IF NOT EXISTS Product(
       ProductID            int PRIMARY KEY,
       ProductName          varchar(100)  NOT NULL,
       ProductDescription   varchar(100)  NOT NULL,
       SerialNumber         int NOT NULL,
       UnitesInStock        int  NOT NULL,
       UnitsOnOrder         int NOT NULL,
       UnitPrice            double NOT NULL,
       SupplierID           int not NULL
)ENGINE=InnoDB DEFAULT CHARSET=latin1;



drop table if exists SupplySchedule;
CREATE TABLE IF NOT EXISTS SupplySchedule(
       ShippingID        int PRIMARY KEY,
       ShippingMode      varchar(50) NOT NULL

)ENGINE=InnoDB DEFAULT CHARSET=latin1;






drop table if exists Supplier;
CREATE TABLE IF NOT EXISTS Supplier(
        SupplierID       int PRIMARY KEY,
        SupplierName     varchar(40) NOT NULL,
        SupplierAddress  varchar(128) NOT NULL,
        ContactName      varchar(128) NOT NULL


   )ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: -1

juergen d
juergen d

Reputation: 204746

order is a reserved word in MySQL. Either use backticks to escape it or use another name.

CREATE TABLE IF NOT EXISTS `Order` (...

Upvotes: 2

Related Questions