多对一约束不起作用 [英] ManyToOne constraint not working

问题描述

我的关系很简单，我的模型是这样的:

公共类项目{[主键，自动递增]公共 int ID { 获取；放;}公共字符串名称{获取；放;}[外键(typeof(User))]公共 int ProjectManagerID { 获取；放;}[多对一]公共用户项目管理器 { get;放;}}公共类用户{公共字符串登录{获取；放;}[一对多]公共列表<项目>项目{得到;放;}}

问题是我可以轻松保存项目，无需保存用户即可引用用户，并且我没有收到任何违反约束的异常.

database.Insert(新项目{名称 = "aaa",项目经理 ID = 8});

我什至可以使用无效的用户 ID 查询我的项目，但我在用户表中没有存储任何记录.另一个有趣的事情是，当我打开这个数据库时，我无法在我的 SQLite 管理工具中存储像上面那样的行，所以我确定我的数据库架构没问题.

这在带有 sqlite-net-extensions 的 sqlite-net PCL 库中正常吗?

更新:我的sqlite版本是3.8.7.4

解决方案

是的，很正常.SQLite-Net Extensions 建立在 SQLite-Net 之上，不支持外键约束，因此没有在数据库层声明外键.

使用核心 SQLite-Net 方法，[ForeignKey] 属性只是一个没有任何限制的整数属性，因此您可以随意修改它而不会出现运行时错误.>

如果您的项目需要数据库层的外键约束，您可以使用Execute方法手动将约束添加到您想要的列中.

更新:

SQLite 中默认禁用外键.要在 SQLite-Net 中启用外键，您必须将以下代码添加到程序的开头.这个语句不会持久化到数据库中，每次应用启动时都必须执行:

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

要检查它是否已启用，您可以使用 PRAGMA foreign_keys:

int result = database.ExecuteScalar("PRAGMA foreign_keys");如果(结果== 1){//启用外键}

启用后，您可以在表声明中添加外键(sqlite 不支持在ALTER 语句) 像这样:

CREATE TABLE child (id 整数主键，parent_id 整数，描述文本，外键 (parent_id) REFERENCES 父 (id));

显然手动创建表会强制您不使用 SQLite-Net CreateTable 语句.

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

解决方案

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.

这篇关于多对一约束不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！