如何在SQLite中默认启用外键级联删除？ [英] How to enable foreign key cascade delete by default in SQLite?

问题描述

SQLite v3.7.5

有没有办法让 SQLite外键与级联删除默认启用？
给定以下例子：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ KEY AUTOINCREMENT，
[Name] VARCHAR（50）UNIQUE NOT NULL
）;

CREATE TABLE [Child]（
[ChildId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL，
[ParentId] INTEGER NOT NULL，
[Name] VARCHAR（50） NOT NULL，
FOREIGN KEY（ChildId）REFERENCES Child（ParentId）ON DELETE CASCADE
）;

我能够启用级联删除的唯一方法是执行 PRAGMA foreign_keys = true 命令在事务之前：

  using（var conn = new SQLiteConnection _conn））
 {
 conn.Open（）; 
 var pragma = new SQLiteCommand（PRAGMA foreign_keys = true;，conn）; 
 pragma.ExecuteNonQuery（）; 
 
 var cmd = new SQLiteCommand（从ParentId ParentId = 1删除，conn）; 
 cmd.ExecuteNonQuery（）; 
 
 
 
 
 
 $ b 
是否有数据库级别的设置可以配置，而不必在每个事务之前调用pragma命令？
 
 
 
我已经看到了启用级联删除的触发器，但是我正在寻找一些可以简单地启用 PRAGMA foreign_keys = true 在数据库级别。
解决方案
 System.Data.SQLite 1.0.66 doesn没有它，但在它的版本库中，它们已经更新到sqlite 3.7.4并且创建了一个新的连接字符串属性Foreign Keys。谁知道什么时候会正式发布？所以你可以在连接字符串中设置。该项目现在住在这里：
  http：/ /system.data.sqlite.org/index.html/doc/trunk/www/index.wiki  
 
SQLite v3.7.5

Is there a way to enable SQLite Foreign Keys with cascade delete enabled by default? 
Given the following example:
CREATE TABLE [Parent] (
[ParentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] VARCHAR(50)  UNIQUE NOT NULL
);

CREATE TABLE [Child] (
[ChildId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[ParentId] INTEGER  NOT NULL,
[Name] VARCHAR(50)  NOT NULL,
FOREIGN KEY(ChildId) REFERENCES Child(ParentId) ON DELETE CASCADE
);
The only way I've been able to enable the cascade delete is to execute the PRAGMA foreign_keys = true command before a transaction:
using( var conn = new SQLiteConnection( _conn ) )
{
    conn.Open();
    var pragma = new SQLiteCommand( "PRAGMA foreign_keys = true;", conn );
    pragma.ExecuteNonQuery();

    var cmd = new SQLiteCommand( "Delete from Parent where ParentId = 1", conn );
    cmd.ExecuteNonQuery();
}
Is there a setting on the database level that can be configured rather than having to call the pragma command before each transaction?

I've seen the triggers to enable cascade deletes, but am looking for something that would simply enable the PRAGMA foreign_keys = true at the database level.
解决方案
System.Data.SQLite 1.0.66 doesn't have it, but in the repository version of it, they have updated to sqlite 3.7.4 and have made a new connection string attribute "Foreign Keys". Who knows when this will be released officially? So you could set this in your connection string. The project lives here now:
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

                        
这篇关于如何在SQLite中默认启用外键级联删除？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！