Question 21694 in Mysql Question posted in
The official MySQL documentation can be found here.

Cannot create my tables due to an error from foreign key – Mysql

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 0

    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)
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search