How to make SQLite foreign keys with SQLite.Net-PCL

Question

In UWP, I enjoy the benefits of using SQLite.Net-PCL, creating classes to be used in apps as ObservableCollections to bind to the GridView. After including SQLiteNetExtensions to build a database with foreign keys, I've noticed that the foreign keys are not truly created when viewing the database in SQLite Maestro. Indexes are created instead. What is the benefit to using the SQLiteNetExtensions if it doesn't really create foreign keys?

Maybe foreign keys are not necessary (later in the app after the database is created) when querying with LAMDA expressions or LINQ. If I executed queries to create tables with foreign keys without using SQLite.Net-PCL, can I still use SQLite.Net-PCL to continue to bind ObservableCollections to GridViews?

Example Database:

[Table("Book")]
public class Book
{
    [PrimaryKey, AutoIncrement, Column("ID")]
    public int ID { get; set; }
    [Column("Name")]
    public string Name { get; set; }

    [ManyToMany]
    public List<Checkout> Checkout { get; set; }
}

[Table("School")]
public class School
{
    [PrimaryKey, AutoIncrement, Column("ID")]
    public int ID { get; set; }
    [Column("Name")]
    public string Name { get; set; }

    [OneToMany]
    public List<Student> Student { get; set; }
    [ManyToMany]
    public List<Checkout> Checkout { get; set; }
}

[Table("Student")]
public class Student
{
    [PrimaryKey, AutoIncrement, Column("ID")]
    public int ID { get; set; }
    [Column("SchoolID"), ForeignKey(typeof(School))]
    public int SchoolID { get; set; }
    [Column("Name")]
    public string Name { get; set; }

    [ManyToOne]
    public School School { get; set; }
}

[Table("Checkout")]
public class Checkout
{
    [PrimaryKey, AutoIncrement, Column("ID")]
    public int ID { get; set; }
    [Column("SchoolID"), ForeignKey(typeof(School))]
    public int SchoolID { get; set; }
    [Column("BookID"), ForeignKey(typeof(Book))]
    public int BookID { get; set; }
}

SQLite is new to me, and there are so many SQLite Nuget packages to choose from. Tutorials are a couple of years old, so there may be something better out now. Thanks in advance.

Phyliciaphylis

Answer 1

Even if you used the entity framework core with the UWP app for your data access foreign keys are not available. By default foreign keys are not enabled in SQLite

https://learn.microsoft.com/en-us/ef/core/providers/sqlite/limitations

https://sqlite.org/foreignkeys.html

Cad

But you can enable foreign keys in SQLite v3.0+ right? Example: conn.Execute("PRAGMA foreign_keys = ON"); – Phyliciaphylis

@Phyliciaphylis that should be actual answer. SQLite does support foreign keys, while in default are disabled... – Null

Answer 2

There you have example from official website how use foreign key in SQLite.

Lucrative

If I executed queries to create tables with foreign keys without using SQLite.Net-PCL, can I still use SQLite.Net-PCL to continue to bind ObservableCollections to GridViews? – Phyliciaphylis

This not have influence on what you wanna do with data stored in database. – Lucrative

Example link now broken – Hairbreadth

Answer 3

FK attribute support is marked as a feature up for grabs.

Also, SQLite-Net Extensions has a ForeignKeyAttribute.

Aesir