ManyToOne constraint not working

Question

I have very simple relationship, my model looks like this:

public class Project
{
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    public string Name { get; set; }

    [ForeignKey(typeof(User))]
    public int ProjectManagerID { get; set; }

    [ManyToOne]
    public User ProjectManager { get; set; }
}

public class User
{
    public string Login { get; set; }

    [OneToMany]
    public List<Project> Projects { get; set; }
}

The problem is that I can easly save project, with its reference to user without saving user, and I don't get any constraint violation exception.

database.Insert(new Project
    {
        Name = "aaa",
        ProjectManagerID = 8
    });

I can even query my project, with invalid user id, but I don't have any record in User table stored. Another interesting thing is that I cannot store row like the one above in my SQLite management tool when I open this database, so I am sure my database schema is ok.

Is this normal in sqlite-net PCL library with sqlite-net-extensions?

UPDATE: My sqlite version is 3.8.7.4

redent84 · Answer 1 · 2015-02-09T12:57:37.373

3

Yes, it is normal. SQLite-Net Extensions is built on top of SQLite-Net, that doesn't have support for foreign key constraints, so there's no foreign key declared at the database layer.

Using core SQLite-Net methods, the [ForeignKey] property is just an integer property that doesn't have any kind of restriction, so you can modify it at will without runtime errors.

If your project requires foreign key constraints at database layer, you could use Execute method to add the constraints to the columns that you want manually.

UPDATE:

Foreign keys are disabled by default in SQLite. To enable foreign keys in SQLite-Net, you have to add the following code to the beginning of your program. This statement is not persisted in the database and must be executed every time the application starts:

database.Execute("PRAGMA foreign_keys = ON");

To check that it's enabled, you can use PRAGMA foreign_keys:

int result = database.ExecuteScalar<int>("PRAGMA foreign_keys");
if (result == 1) {
    // Foreign keys enabled
}

After it's enabled, you can add foreign keys to your tables declarations (sqlite doesn't support adding constraints on ALTER statements) like this:

CREATE TABLE child ( 
    id           INTEGER PRIMARY KEY, 
    parent_id    INTEGER, 
    description  TEXT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Obviously creating the tables manually forces you to not use SQLite-Net CreateTable statements.

edited Feb 09 '15 at 12:57

answered Feb 09 '15 at 11:27

redent84

18,901
4
62
85

Tried executing database.Execute("PRAGMA foreign_keys = ON") but database.Execute("PRAGMA foreign_keys") returns 0 – veeroo Feb 09 '15 at 12:11
Actualy database.Execute("PRAGMA foreign_keys") throws an exception. Problem still exists – veeroo Feb 09 '15 at 12:24
That sounds like a different question. Edited the answer to reflect it. – redent84 Feb 09 '15 at 12:26
OK, my result is 1 but still can save entity with invalid foreign key. It's weird... I'm pretty sure I share the same ISQLConneciton – veeroo Feb 09 '15 at 12:37
Enabling foreign keys is not enough, you have to add the foreign key constraints manually. Edited to clarify it. – redent84 Feb 09 '15 at 12:58
Like I mentioned before, I have foreign keys declared in my schema, using database management tool I cannot insert rows that violates constraints – veeroo Feb 09 '15 at 13:16

score 0 · Answer 2 · answered Feb 09 '15 at 15:05

0

Problem solved. I forgot to copy my database from Resources folder (as template) to app data folder, so technically my databse was being created by SQLite-Net, without any foreign keys. Thanks for help

answered Feb 09 '15 at 15:05

veeroo

752
6
25

ManyToOne constraint not working

2 Answers2

Linked