InsertWithChildrenAsync() and duplicate primary keys

Question

I get a list of objects from a webservice. The data should be stored in a SQLite database. Thereby I can store the first item in the database with InsertWithChildrenAsync(), on the second I get an exception and the app crashes.

Class definition:

public class Color
{
    [PrimaryKey]
    public string Code { get; set; }
    public string Name { get; set; }
}
public class Person
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    [ForeignKey(typeof(Color))]
    public string FavoriteColorId { get; set; }
    [OneToOne(CascadeOperations = CascadeOperation.All)]
    public Color FavoriteColor { get; set; }
}

Initialization:

var dbPath = DependencyService.Get<IStorageService>().GetFilePathForDB();
DependencyService.Get<IStorageService>().DeleteFile(dbPath);
var db = new SQLiteAsyncConnection(dbPath);
await db.CreateTableAsync<Color>();
await db.CreateTableAsync<Person>();

Code demonstrating the issue:

var pers1 = new Person()
{
    Id = 1,
    Name = "John",
    FavoriteColor = new Color() { Code = "FF0000", Name = "Red" }
};

var pers2 = new Person()
{
    Id = 2,
    Name = "Doe",
    FavoriteColor = new Color() { Code = "FF0000", Name = "Red" }
};

await db.InsertWithChildrenAsync(pers1, true);
await db.InsertWithChildrenAsync(pers2, true);

Error message

SQLite.SQLiteException: Constraint

This doesn't happen if I use

var pers2 = new Person()
{
    Id = 2,
    Name = "Doe",
    FavoriteColor = new Color() { Code = "00FF00", Name = "Green" }
};

The problem is that the same primary key is inserted twice. One solution would be to use auto increment, but then the same data is stored multiple times. How can I use the same data for different objects? The data from the webservice is parsed and later stored in the database. I don't hold the objects in memory all the time. Otherwise I could use something like

Color red = new Color { Code = "00FF00", Name = "Green" };
pers1.FavoriteColor = red;
pers2.FavoriteColor = red;

Do I need a many-to-many table? What about deletion? Currently, I'm planning to use DeleteAsync(), but the entry can't be deleted entirely, because another instance is using it. Does the method take this into account?

What are my options?

score 1 · Answer 1 · answered May 21 '18 at 18:27

1

You are inserting this object twice:

new Color() { Code = "00FF00", Name = "Green" }

Therefore you are obtaining a Primary Key constraint error. I'd recommend you to check for existence of that element before inserting it to database.

answered May 21 '18 at 18:27

redent84

18,901
4
62
85

I get that, but I'm only inserting with `InsertWithChildrenAsync()`. If I follow your advice I'd have to check for each property of `Person` if the entry exists. And if yes, fetch that from the database, assign it to the person and then use `InsertWithChildrenAsync()` again? What about deletion? This makes it much more complex and would lead to storing the data manually. `InsertWithChildrenAsync()` seems not to be suitable for any complex data. So there is no easier way? – testing May 22 '18 at 06:13
Well, yes. If you try to insert an already existing object into database you'll get a constrain error, all databases and most ORMs work this way. If you want it to override existing fields, you can use `InsertOrReplace` methods. If you don't want to override, you'll have to check for existence first and insert only if it doesn't already exist. – redent84 May 22 '18 at 10:58

score 0 · Accepted Answer · answered May 22 '18 at 11:01

If you have a complex data structure like me, you can do the following: Primary keys, which don't use the autoincrement flag [PrimaryKey, AutoIncrement], have to be dealed with seperately.

For insertions, don't use CascadeOperations = CascadeOperation.CascadeInsert. Instead you have to insert them manually. E.g.

await StoreColorAsync(db, red);
await db.InsertWithChildrenAsync(pers1, false);

public async Task StoreColorAsync(SQLiteAsyncConnection db, Color color)
{
    if (color == null)
        return;

    var foundItem = await GetColorAsync(db, color.Code);
    if (foundItem != null)
    {
        await db.UpdateAsync(color);
    }
    else
    {
        await db.InsertAsync(color);
    }
}

public async Task<Color> GetColorAsync(SQLiteAsyncConnection db, string colorCode)
{
    var queryResult = await db.Table<Color>().Where(c => c.Code == colorCode).CountAsync();
    if (queryResult > 0)
    {
        return await db.GetAsync<Color>(colorCode);
    }
    else
    {
        return null;
    }
}

For deletion, only use CascadeOperations = CascadeOperation.CascadeDelete for entries with the autoincrement flag. Here I leave the other entries in the database, which will be perhaps reused later. On some special events (e.g. logout) I clear all tables.

Instead of checking for existence in `GetColorAsync()` you could directly use `FindAsync()` - it returns `null` if not found. However, in `StoreColorAsync()` just checking for existence instead of getting the item would be much better. — maf-soft, Oct 12 '21 at 10:32

InsertWithChildrenAsync() and duplicate primary keys

2 Answers2