SQLite.Net Extensions with multiple foreign keys

Question

I am getting the following error when attempting to create a one-to-many relationship that has a multi value foreign key...

**

SQLite.Net.SQLiteException : foreign key mismatch - "Activity" referencing "Node"

**

This is the table SQL (simplified for clarity)...

CREATE TABLE [Activity] (
  [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [InstId_FK] numeric(18,0) NOT NULL, 
  [NodeSeq_FK] numeric(3,0) NOT NULL, 
  [ActivityType] int NOT NULL, 
  CONSTRAINT [Node_FK] FOREIGN KEY([InstId_FK], [NodeSeq_FK]) REFERENCES [Node]([ID], [NodeSeq])ON DELETE CASCADE);

CREATE TABLE [Node] (
  [ID] NUMERIC(18) NOT NULL CONSTRAINT [ID] REFERENCES [Trip]([ID]) ON DELETE CASCADE, 
  [NodeSeq] numeric(3,0) NOT NULL, 
  [Status] int, 
  [Name] nvarchar(30) NOT NULL, 
  CONSTRAINT [sqlite_autoindex_TripNode_1] PRIMARY KEY ([ID], [NodeSeq]));

And my C# Models (again simplified for clarity)...

public class TripNodeActivity : LocationAware
{
    [PrimaryKey, AutoIncrement, ForeignKey(typeof(Node),Order = 1)]
    public int Id { get; set; }

    [Column("InstId_FK")]
    public int InstIdFk { get; set; }

    [Column("NodeSeq_FK"), ForeignKey(typeof(Node),Order = 2)]
    public int NodeSeqFk { get; set; }

    public int ActivityType { get; set; }
}

public class Node
{
    public Node(){Activity = new List<Activity>();}

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

    [PrimaryKey, ForeignKey(typeof(Trip))]
    public int Id { get; set; }

    [PrimaryKey]
    public int NodeSeq { get; set; }

    public int Status { get; set; }
    public String Name { get; set; }
}

This error happens when trying to save the model using:

SQLiteConnection.InsertOrReplaceWithChildren(NodeFromAbove,true)

Can you post the code for the `Activity` class. `LocationAware` class may be important too. — redent84, Aug 13 '14 at 15:54
These CREATE TABLE statements work for me. What have you simplified? — CL., Aug 13 '14 at 16:37

score 1 · Answer 1 · answered Aug 13 '14 at 15:50

Few things there.

First, you have two PrimaryKey attributes in Node class. That is not supported in SQLite-Net.

Second, you are specifying that the PrimaryKey is also a ForeignKey for another table in both classes. That's not what you want to do.

Probably this is more likely what you were trying:

public class TripNodeActivity : LocationAware
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [Column("InstId_FK")]
    public int InstIdFk { get; set; }

    [Column("NodeSeq_FK"), ForeignKey(typeof(Node)]
    public int NodeSeqFk { get; set; }

    public int ActivityType { get; set; }
}

public class Node
{
    public Node(){Activity = new List<Activity>();}

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

    [PrimaryKey]
    public int Id { get; set; }

    public int NodeSeq { get; set; }

    public int Status { get; set; }
    public String Name { get; set; }
}

Hope it helps.

SQLite.Net Extensions with multiple foreign keys

SQLite.Net.SQLiteException : foreign key mismatch - "Activity" referencing "Node"

1 Answers1