4
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 the java database, derby

Does anyone know what I did wrong.

Pirates
  • 115
  • 1
  • 2
  • 7
  • http://stackoverflow.com/questions/3308329/create-autoincrement-key-in-java-db-using-netbeans-ide Refer to post made by Sam – Naranca Jul 04 '16 at 10:10
  • From the code it seems to be mysql – Naranca Jul 04 '16 at 10:19
  • @Naranca I'm not sure which parts of the code suggests this is MySQL, and given the edit it seems to be Derby – Mark Rotteveel Jul 04 '16 at 10:26
  • I might be wrong but the error part is what I consider mysql – Naranca Jul 04 '16 at 10:28
  • @Naranca For MySQL I would expect an error message _"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 'AUTO_INCREMENT' at line 3"_ (if use of `AUTO_INCREMENT` would have been a syntax error in MySQL). – Mark Rotteveel Jul 04 '16 at 10:34

2 Answers2

7

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)
)
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
ddb
  • 2,423
  • 7
  • 28
  • 38
2

Accordign to the Derby definition table page the syntax would be something like this:

 CREATE TABLE PRODUCTS
 (
 ITEM_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,  INCREMENT BY 1)
 ...
 UNIQUE (MAP_ID)
 )

Link here derby docs

cralfaro
  • 5,822
  • 3
  • 20
  • 30