SQLite.Net Update generating error "it has no PK"

SteveSykes

Here is my simple class:

    public class settings
    {

        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }

        public string user_name { get; set; }
        public string password { get; set; }
        public string server { get; set; }

    }

void CreateTables()
{
    database.CreateTable<settings>();
}

It inserts fine, but when I try and update I get "Cannot update settings: it has no PK".
Clearly the class does so what am I doing wrong?

Hmatrix

I had similar issue.
Change
using SQLite; to SQLIte.Net.Attribute;

That solved it for me.`

JustinLovero

@Hmatrix said:
I had similar issue.
Change
using SQLite; to SQLIte.Net.Attribute;

That solved it for me.`

I'm having the same issue, but I do not see a SQLite.Net.Attribute namespace.

SteveSykes

I had to reference sqlite-net separately . Just use NuGet to get it. (notice the hypen)
I also added SQLiteNetExtensions to use relationships. (ForeignKey, Many to Many, One to Many etc.)

    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int ID
    {
        get
        {
            return id;
        }
        set
        {
            id = value;
        }
    }

JustinLovero

@SteveSykes said:
I had to reference sqlite-net separately . Just use NuGet to get it. (notice the hypen)
I also added SQLiteNetExtensions to use relationships. (ForeignKey, Many to Many, One to Many etc.)

I'll give it a try. I am currently using the SQLite.NET Xamarin component. I'll remove that and add the NuGet package.

JustinLovero

I removed the SQLite Xamarin Component and added the sqlite-net NuGet library and the namespace SQLite.Net.Attribute still does not exist.

SteveSykes

You shouldn't need it now. Just a "using SQLite;"
I guess I should have stated I couldn't get HMatirx's solution to work either.

 [SQLite.Table("wo")]
    public class wo
    {
        int id;

        [SQLite.PrimaryKey, SQLite.AutoIncrement]
        public int ID
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
            }
        }
    }

JustinLovero

Ah, I see. That makes more sense. Unfortunately, I'm still seeing the "it has no PK" error. Here's the class:

[SQLite.Table("MyCardStorageObject")]
public class MyCardStorageObject
{
    [SQLite.PrimaryKey, SQLite.AutoIncrement, SQLite.Column("_id")]
    public int Id { get; set; }

    public string Key { get; set; }

    public byte[] Data { get; set; }

    public DateTime DateSaved { get; set;}
}

Stepping through the SQLite.Net code when this error occurs, it appears to be due to a Reflection bug. When SQLite.NET tries to create the column mapping, the Id property is excluded because p.CanWrite == false even though Id obviously has a public setter. So, the _id field is never added to the database table. Inserts work because I guess you can insert without a PrimaryKey column. Updates and deletes, however, require a PK. All of the objects returned from the ORM have an Id of 0, the default int value. The rest of the values are returned correctly.

I already opened an issue for this on the SQLite.NET github repo: https://github.com/praeclarum/sqlite-net/issues/398. Lots more details in there. Note that the example code just uses the attribute names not prefixed with SQLite. It doesn't seem to matter how I use the attributes. It is broken both ways.

Curious if anyone has any input on this. Also, I inherited this code and I'm uncertain why SQLite.Column("_id") is used and I'm wondering if that may be causing the issue.

SteveSykes

That's strange...you're running into the exact problem I had. I removed SQLite.Net and added sqlite-net and added the SQLite to my attributes.
Here is what I wrote in StackOverflow...maybe I'm missing something to tell you that I did there.
http://stackoverflow.com/questions/31590119/sqlite-net-update-generating-error-it-has-no-pk

FranciscoGG

Guys, any solution about this?

bobbyg

Having trouble with this as well... None of the solutions above seem to work for me.

AlexWhite

I used to get the pk message if my linker settings were wrong, play with those, pretty sure the one that works 100% of the time is don't link and the link SDK only works as well, give it a try.

dpedrinha

Still no solution on this?
When I insert to the DB the model PK is actually updated with a non-zero value. But once I try to get any thing from the table, it's always 0.

Marina_Gournianaki

For anyone else having this problem try changing the References. Replace "using SQLite.Net.Attributes;" with "using SQLite;" in the model class when you declare your primary key. Worked for me.

FredyWenger

Any news...?
No suggested solution works for me...

hvaughan

@FredyWenger @dpedrinha @bobbyg @FranciscoGG @JustinLovero

I ran into a similar issue where I would create a new object, it would get a PK. Then when I retrieved the objects from the DB again the PK was 0.

This happened because I initially did not add the PrimaryKey and AutoIncrement annotations to my model's Id property. Even after I did add the annotations, I still got the same issue.

That was because the DB and schema had already been created on the device, so in order to update the table schema, you need to either drop the table and recreate it or delete the DB file and start over.

Deleting the app and reinstalling solves this issue.

dpedrinha

@hvaughan I tried that.

In my case the problem was because I was creating my tables as FTS4. For some reason it doesn't work.

EugeneDrapoguz

Guys,

I have found solution:

1. Removed attributes from the Entities (PrimaryKey, AutoIncrement)

2. Added flags to CreateTable method:
   _dbConnection.CreateTable<OrderEntity>(CreateFlags.ImplicitPK | CreateFlags.AutoIncPK)

ruslan_kutowoj

So its 2017 and I have exact the same problem. All my propertys are public, my IDs have SQLite.Primary /.AutoIncrement attributes, OneToMany relationship using SQLite Extensions, also tried Flags like EugeneDrapoguz recommended and set my linking to none in vs. So what is the problem?

Thats my code:
`var ParentDB = new ParentDB
{
description = "test",

            ChildDB = new List<ChildDB>
                {
                    new ChildDB {description = "blah" }
                }
        };`

SQLiteNetExtensions.Extensions.WriteOperations.InsertWithChildren(GetConnection(), ParentDB, recursive: true);

hvaughan

@komarara Make sure you are completely deleting the app from the device each time you make major schema changes, otherwise the old schema will continue to be used when you run the debugger again.

ruslan_kutowoj

Ok I have added Cascade operation properties and set "preserve application data cache on device between deploys" off. Now im getting a SQLiteNetExtensions.Exceptions.IncorrectRelationship Exception. can you tell my why, because I cant see nothing wrong with my relationship:

[SQLite.Table("Parent")]
    public class ParentDB
    {
        [SQLite.PrimaryKey, SQLite.AutoIncrement]
        public int? Id { get; set; }
        public string description { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public List<ChildDB> ChildDB { get; set; }
    }

 [SQLite.Table("Child")]
    public class ChildDB
    {
        [SQLite.PrimaryKey, SQLite.AutoIncrement]
        public int? Id { get; set; }
        public string description { get; set; }
        [ForeignKey(typeof(ParentDB))]
        public int ParentId { get; set; }
        [ManyToOne(]
        public ParentDB ParentDB { get; set; }
    }

NamyslawSzymaniuk

Da fuq? You have nullable ID, and then NON-nullable ID for relation. That couldn't work I think.

ruslan_kutowoj

Yes because I was frustrated and tried everything I read on stackoverflow, nothing works. Make no difference with or without nullalble int

Garyp01

For me, the problem was caused by the iOS linker stripping the primary key property from my class. Decorating the class with [Preserve (AllMembers = true)] solved the problem. See the Xamarin.Forms documentation on the linker for more information.

codetocode

if you're using sqlite just make sure that you are doing
connection.Delete(object of class settings)

and not
connection.Delete(primary key)

it seems sqlite has problems deleting with primary key and probably deletes the passed object directly