javasqlderby

I get this error "Error code 30000, SQL state 42X01: Syntax error: Encountered "AUTO_INCREMENT" at line 3, column 22." when I create a sql table

CREATE TABLE Products
(
    Item_ID int NOT NULL AUTO_INCREMENT,
    item_make varchar(255) NOT NULL,
    item_model varchar(255),
    brought_in_date varchar(255),
    collected_date varchar(255),
    whats_wrong varchar(255),
    what_was_done varchar(255),
    Price varchar(255),
    CID varchar(255),
    PRIMARY KEY (Item_ID),
    FOREIGN KEY (CID) REFERENCES Customers(CID)
)

When I execute this I get this error

Error code 30000, SQL state 42X01: Syntax error: Encountered "AUTO_INCREMENT" at line 3, column 22.

I am using SQL on a Derby database.

Does anyone know what I did wrong?

Solution

  • Instead of AUTO_INCREMENT (which is MySQL dialect), you should use the SQL standard GENERATED ALWAYS AS IDENTITY as supported by Derby:

    CREATE TABLE Products
(
Item_ID int GENERATED ALWAYS AS IDENTITY not null primary key,
item_make varchar(255) NOT NULL,
item_model varchar(255),
brought_in_date varchar(255),
collected_date varchar(255),
whats_wrong varchar(255),
what_was_done varchar(255),
Price varchar(255),
CID varchar(255),
FOREIGN KEY (CID) REFERENCES Customers(CID)
)