在SQLite中启用外键约束

I'm using SQLite with C# and have some tables with foreign keys defined.

我正在使用SQLite和C＃，并且有一些表定义了外键。

Now, I know that by default foreign key constraints are not enforced in SQLite, but I'd like to turn them ON.

现在，我知道默认情况下，SQLite中没有强制执行外键约束，但我想将它们打开。

Is it possible to do this through code? I have looked up a related question, but I'm not sure how to do it through C# code. I'm using the latest plug-in of SQLite available for Visual Studio 2008 for designing my tables.

是否可以通过代码执行此操作？我查了一个相关的问题，但我不知道如何通过C＃代码来完成。我正在使用Visual Studio 2008中可用的最新SQLite插件来设计我的表。

conn.Open();
SQLiteCommand cmd = new SQLiteCommand("PRAGMA foreign_keys = ON", conn);
cmd.ExecuteNonQuery(); 
conn.Close();

I need this change to persist when this connection is reopened. Is it possible?

重新打开此连接时，我需要保持此更改。可能吗？

8 个解决方案

#1

Finally figured this out from this post. The PRAGMA foreign_key setting does not persist but you can set it every time the connection is made in the ConnectionString. This allows you to use Visual Studio's table adapters.

终于从这篇文章中找到了这个。 PRAGMA foreign_key设置不会保留，但您可以在每次在ConnectionString中建立连接时进行设置。这允许您使用Visual Studio的表适配器。

Make sure you have the latest version (1.0.73.0) of system.data.sqlite installed (1.0.66.0 will not work).
确保安装了最新版本（1.0.73.0）的system.data.sqlite（1.0.66.0不起作用）。
Change your ConnectionString to data source=C:\Dbs\myDb.db;foreign keys=true; (replace C:\Dbs\myDb.db with your sqlite database).
将ConnectionString更改为数据源= C：\ Dbs \ myDb.db; foreign keys = true; （用您的sqlite数据库替换C：\ Dbs \ myDb.db）。

#2

Turn on the pragma:

打开pragma：

PRAGMA foreign_keys = ON;

You can execute this just like any other SQL statement.

您可以像执行任何其他SQL语句一样执行此操作。

#3

I too struggled with this issue. I decided to investigate the full connection string generated in SQLDriverConnect() when connecting to the database. This is what it returned:

我也在努力解决这个问题。我决定在连接到数据库时调查在SQLDriverConnect（）中生成的完整连接字符串。这是它返回的内容：

'Driver={SQLite3 ODBC Driver};Database=C:\Users\Staples\Documents\SQLLiteTest.s3db;StepAPI=;SyncPragma=;NoTXN=;Timeout=;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;FKSupport=;JournalMode=;OEMCP=;LoadExt=;BigInt=;PWD='

As you can see there is a FKSupport property. After adding FKSupport=True; to my connection string it returned this:

如您所见，有一个FKSupport属性。添加FKSupport = True后;我的连接字符串返回了这个：

'Driver={SQLite3 ODBCDriver};Database=C:\Users\Staples\Documents\SQLLiteTest.s3db;StepAPI=;SyncPragma=;NoTXN=;Timeout=;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;FKSupport=True;JournalMode=;OEMCP=;LoadExt=;BigInt=;PWD='

And voila! foreign key contraints are enforced.

瞧！外键约束是强制执行的。

#4

Another solution is to do the "PRAGMA foreign_keys=ON" with every query.

另一个解决方案是对每个查询执行“PRAGMA foreign_keys = ON”。

    SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbSQLite + ";Read Only=False;");
    connection.Open();
    SQLiteCommand mycommand = new SQLiteCommand(connection);
    mycommand.CommandText = "PRAGMA foreign_keys=ON";
    mycommand.ExecuteNonQuery();
    mycommand.CommandText = "DELETE FROM table WHERE ID=x";
    mycommand.ExecuteReader();
    connection.Close();

If you put it in a function to which you pass the CommandText you can reuse it.

如果将它放在传递CommandText的函数中，则可以重用它。

#5

These should provide the information you're looking for:

这些应该提供您正在寻找的信息：

http://www.sqlite.org/faq.html#q22

http://www.sqlite.org/foreignkeys.html#fk_enable

In short, versions before 3.6.19 don't enforce foreign keys at all, but they can be simulated using triggers; starting with 3.6.19, foreign keys can be enforced, but this needs to be enabled per connection using the PRAGMA foreign_keys = ON statement, and sqlite must be compiled with trigger and foreign key support enabled (which I'd expect to be the case for any binary distribution).

简而言之，3.6.19之前的版本根本不强制执行外键，但可以使用触发器模拟它们;从3.6.19开始，可以强制执行外键，但是需要使用PRAGMA foreign_keys = ON语句为每个连接启用，并且必须在启用触发器和外键支持的情况下编译sqlite（我希望是这种情况）对于任何二进制分发）。

#6

Looks like you can just execute the SQL command PRAGMA foreign_keys = ON; on your DB connection just like you would a Select or an Update statement. Though you have to make sure your SQLite was compiled with foreign keys and such. See Here.

看起来你可以只执行SQL命令PRAGMA foreign_keys = ON;您的数据库连接就像您选择或更新语句一样。虽然你必须确保你的SQLite是用外键等编译的。看这里。

#7

Add to your connection string: ";EnforceFKConstraints=Yes|True|1;"

添加到您的连接字符串：“; EnforceFKConstraints = Yes | True | 1;”

#8

-1

In C++ store app the following code helped me to enable PRAGMA foreign_keys
sqlite3_stmt* stmt;
sqlite3_prepare(db, "PRAGMA foreign_keys = ON;", -1, &stmt, 0); 
sqlite3_step(stmt);

I called this after creating db using the call of 

int rc = sqlite3_open16(path->Data(), &db);

#1

Make sure you have the latest version (1.0.73.0) of system.data.sqlite installed (1.0.66.0 will not work).
确保安装了最新版本（1.0.73.0）的system.data.sqlite（1.0.66.0不起作用）。
Change your ConnectionString to data source=C:\Dbs\myDb.db;foreign keys=true; (replace C:\Dbs\myDb.db with your sqlite database).
将ConnectionString更改为数据源= C：\ Dbs \ myDb.db; foreign keys = true; （用您的sqlite数据库替换C：\ Dbs \ myDb.db）。

#2