CREATE TABLE and INSERT INTO?

Databases
1

Ok, this is probably a total newbie question:

I create my table with

CREATE TABLE IF NOT EXISTS tblProtein ( Protein_ID INTEGER PRIMARY KEY, Accession TEXT, …

Now when I insert a record into the database with

INSERT INTO tblProtein VALUES ( ‘AccessionNumber’, …

I get an error as the table has one more value when the ones I specify in the INSERT INTO

I thought the primary key is automatically inserted and incremented???

Surely I can’t specify the primary key before the record is being inserted into the database …

So where am I going wrong?

TiA

Markus

2

Is this SQLite? If so, your create table should have “Protein_ID INTEGER AUTOINCREMENT PRIMARY KEY”.

3

Not familiar with SQLite but the way this is usually done is to name the fields you are supplying
Something like:

INSERT INTO tblProtein ('Accession') VALUES ('the value to be inserted');

This allows you to populate perhaps 3 out of 20 fields, assuming that the remaining fields can be NULL or AUTOINCREMENT.

4

I’m using REALSQLdatabase and SQLite

With AUTOINCREMENT I get an SQL error “near “AUTOINCREMENT”: syntax error”

5

From http://www.w3schools.com/sql/sql_create_table.asp:

[quote]It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,…);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,…)
VALUES (value1,value2,value3,…);[/quote]

However the first form does not seem to work in REALSQLdatabase.

The second one does.

I had gone with the first one as it makes assembling the SQL string easier and faster.

Thanks Jeff.

6

I’d advise ALWAYS ALWAYS ALWAYS name the columns when you insert data

Not doing so is just asking to get hurt