LINQPad: How to use SQLite integer primary key automatic assignment

Question

In SQLite:

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID.

When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine.

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically.

I created a table in SQLite with an integer primary key, and then opened the file in LINQPad. I'm trying to insert a new row into the table via C#:

var episode = new RateableItem()
{
    SourceId = "tal",
    Edition = episodeNum.ToString(),
    Name = episodeName,
    Consumed = false
};

RateableItems.InsertOnSubmit(episode);
SubmitChanges();

In the above example, I haven't specified the pimary key. This fails because LINQPad has mapped the primary key to a non-nullable int, so the generated SQL inserts a 0 into the primary key, which fails because a row with PK 0 already exists.

Is there a way to configure LINQPad with SQLite so that it passes a null for the PK, allowing SQLite to automatically assign the PK value?

I have a couple of SqLite tables. The ones I have created myself all exhibit the behaviour you are seeing. But the sample database from sqlitetutorial.net/sqlite-sample-database (which looks to be defined in the same way), works as expected in LinqPad — sgmoore
– sgmoore, Commented Aug 21, 2017 at 15:35
Thanks @sgmoore, this was helpful! The difference I found was that the tables in the sample db created the PK with AUTOINCREMENT. When I changed added this to my table, it worked - although LINQPad's Schema Explorer doesn't show any difference between the two, I can create a new object without specifying the key. Fortunately I'm okay with the additional behavior that comes with AUTOINCREMENT. — Chris J
– Chris J, Commented Aug 22, 2017 at 0:12

Chris J · Accepted Answer · 2017-08-23 15:32:41Z

3

Thanks to the sample database that @sgmoore posted in comments, I was able to find that declaring the table primary key as AUTOINCREMENT solves my issue.

CREATE TABLE RateableItem (
  RateableItemId INTEGER PRIMARY KEY AUTOINCREMENT,
  SourceId NVARCHAR(32) NOT NULL,
  Name NVARCHAR(255),
  Edition NVARCHAR(16),
  Consumed BOOLEAN NOT NULL
);

When I added AUTOINCREMENT to my table definition, I was able to create a new object without specifying the key. LINQPad's Schema Explorer doesn't show any difference between a primay key with AUTOINCREMENT versus without, but my script compiles and SQLite assigns a value on SubmitChanges().

There are some differences in primary key behavior with AUTOINCREMENT. Fortunately these are fine in my scenario.

answered Aug 23, 2017 at 15:32

Chris J

1632 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harry · Accepted Answer · 2017-08-18 21:43:58Z

0

 var episode = new RateableItem()
 {
     PrimaryKey = null,
     SourceId = "tal",
     Edition = episodeNum.ToString(),
     Name = episodeName,
     Consumed = false
 };

How does that work?

answered Aug 18, 2017 at 21:43

Harry

4,0602 gold badges16 silver badges33 bronze badges

2 Comments

Chris J Over a year ago

LINQpad won't run the script because PrimaryKey isn't nullable.

Chris J Over a year ago

Not seriously. I was hoping for something workable with just LINQPad and the the IQ driver. My workaround at this point is to get the max primary key value, increment, and explicitly set it for inserts.

Collectives™ on Stack Overflow

LINQPad: How to use SQLite integer primary key automatic assignment

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related