How to auto increment database column in Java derby?

Question

Here's the SQL I've tried so far to create the table

CREATE TABLE tblPosts (nId INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
strContent VARCHAR(140) NOT NULL,
strLink VARCHAR(200),
strImage VARCHAR(200));

using

String sql = "INSERT INTO tblPosts VALUES ('" + textFieldContent.getText() + "', '" + 

textFieldLink.getText() + "', '" + textFieldImage.getText() + "')";

I get an error telling me I'm not providing the nId column value which I'm not but if the column auto-increments I'm not supposed to right?

I've also tried using the IDE to create the table on the database as described here

Alter a table column with auto increment by 1 in derby

Same response though. Any suggestions?

I would specify the column names in your INSERT command. Also, I would post the actual error. — Steve Smith, Feb 17 '17 at 16:17

score 4 · Answer 1 · answered Aug 18 '18 at 18:11

4

You need to include Start with 1, Increment by 1 Like this

CREATE TABLE tblPosts (nId INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY(Start with 1, Increment by 1), 
strContent VARCHAR(140) NOT NULL, 
strLink VARCHAR(200)

answered Aug 18 '18 at 18:11

kelvin andre

395
5
11

score 1 · Accepted Answer · answered Feb 17 '17 at 16:23

1

I would guess that since you're not specifying the column names in the SELECT, it is getting confused as to which columns the data should go in. I would specify the column names in your INSERT command.

answered Feb 17 '17 at 16:23

Steve Smith

2,244
2
18
22

Apparently I need to wait 6 minutes to mark it as so. – luigivampa Feb 17 '17 at 16:24

score 0 · Answer 3 · answered Aug 14 '20 at 11:53

0

You need to set THAT auto_increment column to DEFAULT like this:

String sql = "INSERT INTO tblPosts VALUES ( DEFAULT, '" + textFieldContent.getText() + "', '" + 

textFieldLink.getText() + "', '" + textFieldImage.getText() + "')";

answered Aug 14 '20 at 11:53

ABFovox

39
4

How to auto increment database column in Java derby?

3 Answers3