[Solved] MySQL – Nonsense Syntax Errors

by


All the commas before UNIQUE are wrong. Commas are used to separate the columns, not to separate options for a specific column.

CREATE TABLE IF NOT EXISTS DONUT (
    Donut_ID            INT             NOT NULL UNIQUE,
    Donut_Name          VARCHAR (20)    NOT NULL UNIQUE,
    Description         VARCHAR (20)    NOT NULL UNIQUE,
    Unit_Price          NUMERIC (4,2)   NOT NULL,
    PRIMARY KEY (Donut_ID));

CREATE TABLE IF NOT EXISTS CUSTOMER (
    Customer_ID         INT             NOT NULL UNIQUE,
    Last_Name           VARCHAR (20)    NOT NULL,
    First_Name          VARCHAR (20)    NOT NULL,
    Address             VARCHAR (50)    NOT NULL,
    Apt                 VARCHAR (5),
    City                VARCHAR (20)    NOT NULL,
    State               CHAR (2)        NOT NULL,
    Zip                 NUMBER (5)      NOT NULL,
    Home_Phone          NUMBER (10),
    Mobile_Phone        NUMBER (10),
    Other_Phone         NUMBER (10),
    PRIMARY KEY (Customer_ID));

CREATE TABLE IF NOT EXISTS SALES_ORDER (
    Sales_Order_ID      INT             NOT NULL UNIQUE,
    Sales_Order_Date    DATE            NOT NULL,
    Sales_Order_Notes   VARCHAR (100),
    Customer_ID         INT             NOT NULL,
    PRIMARY KEY (Sales_Order_ID),
    FOREIGN KEY (Customer_ID) REFERENCES CUSTOMER (Customer_ID));

CREATE TABLE IF NOT EXISTS ORDER_LINE_ITEM (
    Donut_ID            INT             NOT NULL,
    Sales_Order_ID      INT             NOT NULL,
    Quantity            INT             NOT NULL,
    PRIMARY KEY (Donut_ID, Sales_Order_ID),
    FOREIGN KEY (Donut_ID) REFERENCES DONUT (Donut_ID),
    FOREIGN KEY (Sales_Order_ID) REFERENCES SALES_ORDER (Sales_Order_ID));

BTW, the UNIQUE option is not necessary for primary keys, they’re automatically made unique. And in ORDER_LINE_ITEM, if the primary key is (Donut_ID, Sales_Order_ID) you probably don’t want to make each of those columns unique by themselves. This prevents the same donut from being in multiple orders, or an order having more than one donut type. Similarly, Customer_ID should not be unique in SALES_ORDER; a customer can obviously have more than one order.

Your FOREIGN KEY lines were missing the references of the foreign keys. And the tables with the foreign keys need to be created after the tables they reference, so I moved SALES_ORDER down to after CUSTOMER.

3

solved MySQL – Nonsense Syntax Errors