Cannot create my tables due to an error from foreign key-CodePudding

I got this error from mysql its not creating my table as suppose since there is an error from it the code is the following

CREATE TABLE customers (
  customer_number INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  address VARCHAR(60) NOT NULL,
  shipping_adress VARCHAR(60) NOT NULL,
  billing_adress VARCHAR(60) NOT NULL,
  balance_due DATE NOT NULL,
  UNIQUE (customer_number)
);

CREATE TABLE orders (
  order_number INT PRIMARY KEY NOT NULL,
  customer_number VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  order_total_cost INT NOT NULL,
  FOREIGN KEY (customer_number) REFERENCES customers(customer_number)
);

CREATE TABLE inventory (
  product_number INT PRIMARY KEY,
  product_name VARCHAR(60) NOT NULL,
  product_measure FLOAT NOT NULL,
  product_price FLOAT NOT NULL,
);

CREATE TABLE ordered_products (
  ordered_id INT PRIMARY KEY,
  order_number INT NOT NULL,
  product_number INT NOT NULL, 
  quantity INT NOT NULL CHECK (QUANTITY > 0),
  unit_price FLOAT NOT NULL,
  FOREIGN KEY (order_number) REFERENCES orders(order_number),
  FOREIGN KEY (product_number) REFERENCES inventory(product_number)
);

and the errror is as follows ERROR 1064 (42000) at line 20: 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 ')' at line 6

CodePudding user response：

customer_number is a INT in customers table but VARCHAR(20) in the orders table

Ran on MySQL Compiler and it was successful

CREATE TABLE customers (
  customer_number INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  address VARCHAR(60) NOT NULL,
  shipping_adress VARCHAR(60) NOT NULL,
  billing_adress VARCHAR(60) NOT NULL,
  balance_due DATE NOT NULL
);

CREATE TABLE orders (
  order_number INT PRIMARY KEY NOT NULL,
  customer_number INT NOT NULL,
  order_date DATE NOT NULL,
  order_total_cost INT NOT NULL,
  FOREIGN KEY (customer_number) REFERENCES customers(customer_number)
);

CREATE TABLE inventory (
  product_number INT PRIMARY KEY,
  product_name VARCHAR(60) NOT NULL,
  product_measure FLOAT NOT NULL,
  product_price FLOAT NOT NULL
);

CREATE TABLE ordered_products (
  ordered_id INT PRIMARY KEY,
  order_number INT NOT NULL,
  product_number INT NOT NULL, 
  quantity INT NOT NULL CHECK (QUANTITY > 0),
  unit_price FLOAT NOT NULL,
  FOREIGN KEY (order_number) REFERENCES orders(order_number),
  FOREIGN KEY (product_number) REFERENCES inventory(product_number)
);

CodePudding user response：

So the thing is I added a extra comma in the

CREATE TABLE inventory (
  product_number INT PRIMARY KEY,
  product_name VARCHAR(60) NOT NULL,
  product_measure FLOAT NOT NULL,
  product_price FLOAT NOT NULL,
);

So I just removed the comma from product_price FLOAT NOT NULL

and it worked.

Commas can really be a pain if not using a mysql workbench