Vairis Vairis - 6 months ago 7
SQL Question

Problems Creating simple database with relations

Good day!



I am trying to make Internet Book Store database, for project, but I am facing error, that I can't locate.


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 INT AUTO_INCREMENT PRIMARY KEY,
customerID INT, courierID INT, o' at line 1


Here is my database I am trying to make.

Create database veikals;

CREATE TABLE Customer (
customerID INT AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
tel_number VARCHAR(8) NOT NULL,
email VARCHAR(50) NOT NULL,
postal_code VARCHAR(4) NOT NULL,
Adress VARCHAR (255) NOT NULL
);

CREATE TABLE Courier (
courierID INT AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
tel_number VARCHAR(8) NOT NULL
);


Up to this moment, everything works fine, but after this code part, MYSQL finds error.

CREATE TABLE Order (
orderID INT AUTO_INCREMENT PRIMARY KEY,
customerID INT,
courierID INT,
order_date DATE,
reacive_date DATE,
payment_method VARCHAR(20) NOT NULL,
recieve_adress VARCHAR(255) NOT NULL,
Foreign key (customerID) references Customer(customerID),
Foreign key (courierID) references Courier(courierID)
);


And here is last 2 parts of database, that I have not tested yet, could they work out?

CREATE TABLE Books (
ISBN VARCHAR(30) PRIMARY KEY,
Genre VARCHAR(20),
Title VARCHAR(255),
author_name VARCHAR(30),
author_surename VARCHAR(30),
publisher VARCHAR(255),
published date,
price DECIMAL(10,2)
);

CREATE TABLE Cart (
orderID Int PRIMARY KEY,
ISBN VARCHAR(30) PRIMARY KEY,
amount Int,
Foreign key (orderID) references Order(orderID),
Foreign key (ISBN) references Books(ISBN)
);


So, my question here is:


  1. What is making error in my code, and what should I do?

  2. Should I remove or add anything that I might Forget?



Thank you for your help.

Answer

Well your Problem is, that the keyword Order is pre-defined in mySQL. So when you're trying to create a table with the name Order SQL interprets it as the command to order a set of selected data. (e.g. SELECT * FROM TABLE ORDER BY column) That's why you get this error.

Try to give the table another name and you'll be fine ;)

Comments