SqlLite – Primary keys and Identity values

Can I have a primary key in a SqlLite table?

SqLite supports primary keys. These are used to index a table by a particular field’s unique values.

You specify the PRIMARY KEY command when the table is created, after the type of the field.

The following example creates a table called test that has a primary key field called “Id” and another field called… wait for it… “AnotherField”.

"create table TestTable (
    Id INTEGER PRIMARY KEY,
    AnotherField TEXT);"

Does SqLite support autonumbered primary keys (identity values)?

You can of course create identity fields in a SqLite database. These will contain a unique generated value for each row inserted into the table. You do not provide the value on inserting rows, the value is determined by the database.

What is the new identity value of the row I just inserted?

Most SQL languages have a way to get back the identity value of a newly added row.

In SqlLite it is:

SELECT last_insert_rowid()

This will return the identity value of the record just inserted. You can then use that value as a foreign key in other, related, tables.

No Comments

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

You must be logged in to post a comment.

WordPress Themes