So I'm trying to create a Derby database in Netbeans so that I can later build an application for interacting with it and I'm stuck on a confusing, but probably simple error.
Specifically, I'm trying to create an auto-increment sequence for a PK (Primary Key) column.
My code for creation of the sequence is below.
--Creating sequences here
--Pk sequence for characters table here
CREATE OR REPLACE SEQUENCE characters_pk_sequence MINVALUE 1 START WITH 1 INCREMENT BY 1;
-- End of PK sequence for characters table here
The console error is below.
Error code 30000, SQL state 42X01: Syntax error: Encountered "OR" at line 1, column 8.
This isn't the only method I tried, I tried other methods as described by the internet but they didn't work either. What am I doing wrong here, folks?
EDIT: SOLVED Guys. I can't accept my own answer however for another 2 Days.
(Answering my own question, feels weird.) So I figured out how to do the thing that I was trying to do. (i.e. auto-incrementing PK) This version of the database software didn't work the same way as the previous version that I used the first time awhile back.
I found an appropriate answer in this stackoverflow question.
To create an auto-incrementing key in Derby, you can do it in the table like so
CREATE TABLE characters(
hp INTEGER,
hp_max INTEGER,
armor INTEGER,
-- Various other table columns, etc.
char_pk INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1)
);
Doing that allowed me to create an auto-incrementing PK, and without having to specify it elsewhere, which is pretty awesome.