mysql Cannot create my tables due to an error from foreign key

z5btuh9x 于 2022-12-22 发布在 Mysql

关注(0)|答案(2)|浏览(100)

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

mysql

来源：https://stackoverflow.com/questions/74857604/cannot-create-my-tables-due-to-an-error-from-foreign-key

2条答案

按热度按时间

y53ybaqx1#

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)
);

赞(0）回复(0）举报 2022-12-22

lx0bsm1f2#

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

赞(0）回复(0）举报 2022-12-22

我来回答

mysql Cannot create my tables due to an error from foreign key

2条答案

相关问题

热门标签

最新问答