SQLite; "Cannot add a PRIMARY KEY column"-Exception

Question

today I have started coping with databases. I've installed SQLite and SQLite-net. I am Programming a Windows 8.1 App using C#.

All I have is the following:

A Model:

public class Subject
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public string Name { get; set; }    
}

And the OnLaunched-Event in App.Xaml.cs contains:

protected override void OnLaunched(LaunchActivatedEventArgs e)
{
[...]
    // Get a reference to the SQLite database
    DBPath = Path.Combine(
        Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Subjects.s3db");
    // Initialize the database if necessary
    using (var db = new SQLite.SQLiteConnection(DBPath))
    {
        // Create the tables if they don't exist
        db.CreateTable<Subject>();
    }
[...]
}

When I launch this I get the following error after db.CreateTable(); is executed:

Cannot add a PRIMARY KEY column.

What is going wrong here? I really would appreciate your help.

Thank you very much.

Greetings, FunkyPeanut

Answer 1

I believe this is happening because you've changed the schema of the DB table by adding or removing a field from your Subject class. I.e. you've added the Id property after having already run the application to create the Subject table.

This is why it works with a new DB file. You'll either need to modify the schema (which in SQLite involves creating a new table, copy data from the existing table, deleting existing table and then renaming the new table), or simply delete your old DB file and create a new one.

Answer 2

I had the same issue ... this is so stupid but it was because when I copied / pasted code from another table I forgot to change its name ......

Before

[Table("CopiedTable")]

After

[Table("MyNewTable")]