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.

score 2 · Accepted Answer · answered Apr 22 '17 at 15:02

2

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

answered Apr 22 '17 at 15:02

Ken Tucker

4,126
1
18
24

2

But you can enable foreign keys in SQLite v3.0+ right? Example: conn.Execute("PRAGMA foreign_keys = ON"); – detailCode Apr 24 '17 at 18:15
@detailCode that should be actual answer. SQLite does support foreign keys, while in default are disabled... – Tomas Sep 05 '17 at 21:43

Szymson · Answer 2 · 2018-06-15T20:56:50.083

1

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

edited Jun 15 '18 at 20:56

answered Apr 24 '17 at 18:22

Szymson

990
1
13
28

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? – detailCode Apr 24 '17 at 20:01
This not have influence on what you wanna do with data stored in database. – Szymson Apr 26 '17 at 10:46
Example link now broken – arame3333 Jun 07 '18 at 08:24

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

2 Answers2