InsertOrReplaceWithChildren throws Constraint exception for composite key

[James Howe]

I have the following model

public class InvoiceLineItem
{
    [PrimaryKey, ForeignKey(typeof(Product)), Required]
    public int ProductId { get; set; }
    [PrimaryKey, ForeignKey(typeof(Invoice)), Required]
    public int InvoiceId { get; set; }
    ...

    // navigation properties
    [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
    public Product Product { get; set; }
}

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

    // navigation properties
    [OneToMany(CascadeOperations = CascadeOperation.CascadeRead | CascadeOperation.CascadeInsert)]
    public List<InvoiceLineItem> LineItems { get; set; }
}

If I create a new Invoice and add a couple of InvoiceLineItem objects and then attempt to save via

conn.RunInTransaction (() => conn.InsertOrReplaceWithChildren(invoice));

Everything appears to go fine until it hits the UpdateWithChildren call in the InsertWithChildrenRecursive method - the log looks something like

#!
Executing: insert  into "Invoice" (...) values (...)
Executing: insert OR REPLACE into "InvoiceLineItem"("ProductId","InvoiceId",...) values (?,?,...)
Executing: insert OR REPLACE into "InvoiceLineItem"("ProductId","InvoiceId",...) values (?,?,...)
Executing: update InvoiceLineItem set InvoiceId = ? where ProductId in (?,?)
  0: 6
  1: 28
  2: 2
Executing: rollback

No idea why it throws a constraint error as that command the command is valid - if I run the update command that fails directly against the SQLite DB it works without error.

[Guillermo Gutiérrez]

Composite keys are currently not supported in SQLite-Net Extensions and as far as I know SQLite-Net PCL doesn't support it yet.

[James Howe]

SQLite-Net certainly supports composite keys as I've been using them up to last week. Before I upgraded Extensions to get the CascadeOptions stuff I was working around this by just manually inserting the composite key records.

I don't think this is a million miles away from working, I think (I will need to verify this) if I remove the Product navigation key from InvoiceLineItem and save it works but only if the records don't already exist.

[James Howe]

This doesn't appear to work in general actually. I removed the composite key model and replaced with

public class InvoiceLineItem
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [ForeignKey(typeof(Invoice)), Required]
    public int InvoiceId { get; set; }
    ...
}

This lets me create the record but if I try to update an existing record via InsertOrReplaceWithChildren(invoice) it fails on the same UpdateWithChildren method as before. However, interestingly it does appear to work if I have ManyToMany navigation properties e.g.

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

    // navigation properties
    [ManyToMany(typeof(CustomerContact), CascadeOperations = CascadeOperation.CascadeRead | CascadeOperation.CascadeInsert)]
    public List<Contact> Contacts { get; set; }
    [ManyToMany(typeof(CustomerAddress), CascadeOperations = CascadeOperation.CascadeRead | CascadeOperation.CascadeInsert)]
    public List<Address> Addresses { get; set; }
}

public class CustomerContact
{
    [ForeignKey(typeof(Customer))]
    public int CustomerId { get; set; }

    [ForeignKey(typeof(Contact))]
    public int ContactId { get; set; }
}

public class CustomerAddress
{
    [ForeignKey(typeof(Customer))]
    public int CustomerId { get; set; }

    [ForeignKey(typeof(Address))]
    public int AddressId { get; set; }
}

This works for both creating new items & updating/removing existing.

Note - I did forget to mention that I am running this on Mono

[Guillermo Gutiérrez]

Hi James, composite key relationships are not going to work because there's currently no way to declare composite foreign keys. Currently the way to go is the way you implemented it last time, replacing the two composite keys with an AutoIncrement primary key.

Usually the 'constraint' error from SQLite-Net is due to duplicated PrimaryKey or Unique fields. Make sure that all these fields have a correct value or a AutoIncrement attribute.

If you can attach a sample reproducing the issue I may be able to help you further.

Kind regards

[Stefan Charsley]

Hi James, Guillermo

I have started working on a new application and have been studying up on SQLite Extensions.

I came across this issue and wanted to put my two cents in and see if my solution works (it is currently untested as I haven't finished modelling the database).

This is from one of my models.

From the documentation I came up with this:

    public class SessionChild
    {
        [Indexed(Name = "SessionID_ChildID_PK", Order = 1, Unique = true), ForeignKey(typeof(Session))]
        public int SessionID { get; set; }

        [Indexed(Name = "SessionID_ChildID_PK", Order = 2, Unique = true), ForeignKey(typeof(Child))]
        public int ChildID { get; set; }

        [NotNull]
        public DateTime DateStart { get; set; }

        [NotNull]
        public DateTime DateEnd { get; set; }
    }

From my understanding, this would effectively be a Composite Foreign Key