Shouldn't have to specify primary key explicitly

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Shouldn't have to specify primary key explicitly

Scott Robertson
SQLite is supposed to autoincrement by default when a column is defined
as "INTEGER PRIMARY KEY" according to everything I've read. But I've
only gotten this to work if I let SQLite create its own PK column. If I
have an explicit PK column, I am expected to specify an ID myself. What
am I missing? I don't know why I'm getting this error. Thanks.


CREATE TABLE test1 (name TEXT, date DATE);

INSERT INTO test1 VALUES ('Barney', 1999

);


SELECT * FROM test1;

name date

---------- ----------

Barney 1999


SELECT *, rowid FROM test1;

name date rowid

---------- ---------- ----------

Barney 1999 1


CREATE TABLE test2 (

id INTEGER PRIMARY KEY,

book text,

page INTEGER

);


INSERT INTO test2 VALUES (

'Fletch',

245

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

1,

'Dragnet',

17

);


SELECT *, rowid FROM test2;

id book page id

---------- ---------- ---------- ----------

1 Dragnet 17 1


INSERT INTO test2 VALUES (

'Lord of the Rings',

327

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

9,

'Lord of the Rings',

327

);


SELECT *, rowid FROM test2;

id book page id

---------- ---------- ---------- ----------

1 Dragnet 17 1

9 Lord of th 327 9


--

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Shouldn't have to specify primary key explicitly

Hick Gunter
Supply a NULL for the INTEGER PRIMARY KEY to tell SQLite to "figure it out for yourself" (c) Siddharta Gautama "Buddha"

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Scott Robertson
Gesendet: Donnerstag, 28. Juni 2018 13:48
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Shouldn't have to specify primary key explicitly

SQLite is supposed to autoincrement by default when a column is defined as "INTEGER PRIMARY KEY" according to everything I've read. But I've only gotten this to work if I let SQLite create its own PK column. If I have an explicit PK column, I am expected to specify an ID myself. What am I missing? I don't know why I'm getting this error. Thanks.


CREATE TABLE test1 (name TEXT, date DATE);

INSERT INTO test1 VALUES ('Barney', 1999

);


SELECT * FROM test1;

name date

---------- ----------

Barney 1999


SELECT *, rowid FROM test1;

name date rowid

---------- ---------- ----------

Barney 1999 1


CREATE TABLE test2 (

id INTEGER PRIMARY KEY,

book text,

page INTEGER

);


INSERT INTO test2 VALUES (

'Fletch',

245

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

1,

'Dragnet',

17

);


SELECT *, rowid FROM test2;

id book page id

---------- ---------- ---------- ----------

1 Dragnet 17 1


INSERT INTO test2 VALUES (

'Lord of the Rings',

327

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

9,

'Lord of the Rings',

327

);


SELECT *, rowid FROM test2;

id book page id

---------- ---------- ---------- ----------

1 Dragnet 17 1

9 Lord of th 327 9


--

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Shouldn't have to specify primary key explicitly

Tim Streater-3
In reply to this post by Scott Robertson
On 28 Jun 2018, at 12:48, Scott Robertson <[hidden email]> wrote:

> SQLite is supposed to autoincrement by default when a column is defined
> as "INTEGER PRIMARY KEY" according to everything I've read. But I've
> only gotten this to work if I let SQLite create its own PK column. If I
> have an explicit PK column, I am expected to specify an ID myself. What
> am I missing? I don't know why I'm getting this error. Thanks.

You've defined the table with three cols so you have to provide three values unless you name the cols you wish to fill. To get SQLite to auto increment, use NULL as the value fo your id column:

INSERT INTO test2 VALUES (NULL, 'Fletch', 245);



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Shouldn't have to specify primary key explicitly

curmudgeon
INTEGER PRIMARY KEY doesn’t default to autoincrement. It’s used in place of the automatically created autoincrement rowid but you have to supply the values (I.e. they’re not created automatically).

On 28 Jun 2018, at 12:48, Scott Robertson <[hidden email]> wrote:

> SQLite is supposed to autoincrement by default when a column is defined
> as "INTEGER PRIMARY KEY" according to everything I've read. But I've
> only gotten this to work if I let SQLite create its own PK column. If I
> have an explicit PK column, I am expected to specify an ID myself. What
> am I missing? I don't know why I'm getting this error. Thanks.

You've defined the table with three cols so you have to provide three values unless you name the cols you wish to fill. To get SQLite to auto increment, use NULL as the value fo your id column:

INSERT INTO test2 VALUES (NULL, 'Fletch', 245);



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Shouldn't have to specify primary key explicitly

curmudgeon
>INTEGER PRIMARY KEY doesn’t default to autoincrement. It’s used in place of
the automatically created >autoincrement rowid but you have to supply the
values (I.e. they’re not created automatically).

I stand corrected. If you supply null for the integer primary key it will
assign the highest rowid + 1.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Shouldn't have to specify primary key explicitly

Simon Slavin-3
In reply to this post by Scott Robertson
On 28 Jun 2018, at 12:48pm, Scott Robertson <[hidden email]> wrote:

> CREATE TABLE test2 (
> id INTEGER PRIMARY KEY,
> book text,
> page INTEGER
> );
>  
> INSERT INTO test2 VALUES ('Lord of the Rings', 327);
>
> Error: table test2 has 3 columns but 2 values were supplied
>
> INSERT INTO test2 VALUES (9, 'Lord of the Rings', 327);

In the first example you declared a three-column table but supplied two values.

In the second example you explicitly stated that you wanted to supply values for all the declared columns, so SQLite used the values you supplied.

To avoid this do either of the following:

    INSERT INTO test2 VALUES (NULL, 'Lord of the Rings', 327);
    INSERT INTO test2 (book, page) VALUES ('Lord of the Rings', 327);

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users