ROWID INTEGER PRIMARY KEY AUTOINCREMENT - How to insert values?

Question

I created an SQLite table in Java:

create table participants (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, col1,col2);

I tried to add rows :

insert into participants values ("bla","blub");

Error:

java.sql.SQLException: table participants has 3 columns but 2 values were supplied

I thought ROWID would be generated automatically. I tried another solution:

PreparedStatement prep = conn.prepareStatement("insert into participants values (?,?,?);");
Integer n = null;
prep.setInt(1,n);
prep.setString(2, "bla");
prep.setString(3, "blub");
prep.addBatch();
prep.executeBatch();

I received a null pointer exception at prep.setInt(1,n);. Do you see the fault?

score 71 · Accepted Answer · answered Nov 24 '11 at 00:11

71

Have you tried indicating to which fields of the table the parameters you are passing are supposed to be related?

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

In your case maybe something like:

INSERT INTO participants(col1, col2) VALUES ("bla","blub");

answered Nov 24 '11 at 00:11

seth

1,401
1
17
28

If you're doing a schema migration and using a select statement for your insertion you can do `insert into participants(col1, col2) select col1, col2 from t1_backup;` – mlissner Sep 25 '15 at 17:56

score 34 · Answer 2 · edited Sep 29 '15 at 13:18

34

Easiest way without using column names will be using null in the place of autoincreament is like this

insert into table values (null, col1, col2)

if you have already set the first column as autoincrement, it will work fine.

edited Sep 29 '15 at 13:18

OhadM

4,687
1
47
57

answered Sep 24 '15 at 10:20

Padhu

1,590
12
15

score 9 · Answer 3 · edited May 23 '17 at 12:02

9

found a working solution here:

PreparedStatement prep = conn.prepareStatement("insert into participants values ($next_id,?,?);");
prep.setString(2, "bla");
prep.setString(3, "blub");

edited May 23 '17 at 12:02

Community

1
1

answered Nov 24 '11 at 13:30

Anthea

3,741
5
40
64

score 3 · Answer 4 · answered Aug 28 '15 at 14:03

The reason to your error is that SQL Inserts expect you to provide the same number of values as there are columns in the table when a column specifier is not used.

i.e. when you write a SQL query like this:

INSERT INTO TableName VALUES(a1,a2, ...)

.. the values have to be in the exact same order as in the table definition (and also the same amount). The reason for this is to avoid ambiguity and reduce the numbers of errors.

In your case you have an auto increment column which you don't want to specify a value for. That is of course possible, but following the rules above you need to specify column names:

INSERT INTO TableName (columnName1, columnName2) VALUES(Value1, Value2);

Super Kai - Kazuya Ito · Answer 5 · 2022-09-29T16:49:39.060

For example, you create "person" table with "id" with AUTOINCREMENT and "name" as shown below:

CREATE TABLE person (    -- Here
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  name TEXT
);

Then, set NULL to "id" of AUTOINCREMENT as shown below. *You can set NULL to "id" which has NOT NULL because "id" has INTEGER PRIMARY KEY:

INSERT INTO person VALUES (NULL, "John"), (NULL, "Tom");

Now, you can insert rows as shown below:

sqlite> SELECT * FROM person;
┌────┬──────┐
│ id │ name │
├────┼──────┤
│ 1  │ John │
│ 2  │ Tom  │
└────┴──────┘

ROWID INTEGER PRIMARY KEY AUTOINCREMENT - How to insert values?

5 Answers5

Linked

Related