unexpected beginning of statement near(prod_id)

CREATE TABLE product
(
  product_id int primary key,
  product_name varchar(50) NOT null,
  category varchar(50)
  );
  CREATE TABLE orders
  (
    ORDER_id int primary key,
    prod_id int NOT null,
    quantity INT
    )
    CONSTRAINT fk_product_id
    FOREIGN KEY (prod_id)
    REFERENCES product (prod_id)
    );

Answer

The constraint should be part of the table definition:

CREATE TABLE orders (
    ORDER_id int primary key,
    product_id int NOT null,
    quantity INT,
    CONSTRAINT fk_product_id
        FOREIGN KEY (product_id) REFERENCES product(product_id)
);

Note that I named the column to product_id and fixed the reference to product as well. I’m a fan of foreign keys and primary keys having the same name when possible.

You could also define it using ALTER TABLE after the table definition.

Answer

Leave a Reply Cancel reply