ON DELETE CASCADE在C#中不起作用 [英] ON DELETE CASCADE not working in c#

问题描述

当我从第一个表中使用C#中的 ExecuteNonQuery()删除主键时，它不会影响第二个表.但是，当我对SQLiteStudio执行相同操作时，的确会影响第二张表.

When I delete a primary key with ExecuteNonQuery() in C# from the first table it doesn't affect the second table. But when I do the same with SQLiteStudio it does affect the second table.

这是我的SQLite代码:

Here is my SQLite code:

CREATE TABLE Zdravila(
   ID INTEGER PRIMARY KEY,
   NACIONALNA_SIFRA INTEGER UNIQUE NOT NULL,
   IME_ZDRAVILA TEXT,
   POIMENOVANJE_IZDELKA TEXT NOT NULL,
   SIFRA_REZIM_IZDAJE INTEGER NOT NULL,
   ATC_OZNAKA TEXT   NOT NULL,
   NAZIV_POTI_UPORABE TEXT NOT NULL,
   FOREIGN KEY(NACIONALNA_SIFRA) REFERENCES LastnostiZdravila(NACIONALNA_SIFRA) ON DELETE CASCADE ON UPDATE CASCADE
   );

CREATE TABLE LastnostiZdravila(
   NACIONALNA_SIFRA INTEGER PRIMARY KEY,
   NAZIV_FARMACEVTSKE_OBLIKE  TEXT NOT NULL,
   KOLICINA_OSNOVNE_ENOTE  INTEGER NOT NULL CHECK (KOLICINA_OSNOVNE_ENOTE > 0),
   OZNAKA_OSNOVNE_ENOTE    TEXT NOT NULL,
   PAKIRANJE   TEXT NOT NULL,
   SIFRA_IMETNIKA_DOVOLJENJA  TEXT NOT NULL
   );

PRAGMA foreign_keys = ON;

还有我的C#代码:

SQLiteConnection conn = new SQLiteConnection(poveziZBazo);
                SQLiteCommand sqlQuery = new SQLiteCommand(sql);
                sqlQuery.Connection = conn;
                conn.Open();
                SQLiteTransaction trans;
                trans = conn.BeginTransaction();

                sqlQuery.CommandText = sql;
                j = sqlQuery.ExecuteNonQuery();
                trans.Commit();
                sqlQuery.Dispose();
                conn.Close();

我的查询:从LastnostiZdravila删除，其中NACIONALNA_SIFRA =" +'" + NacionalnaSifraBrisanje +'" +;";

(我检查NacionalnaSifraBrisanje的sql命令)

(I check NacionalnaSifraBrisanje for sql commands)

推荐答案

根据 http://sqlite.org/pragma.html#pragma_foreign_keys :

从SQLite 3.6.19版本开始，外键强制的默认设置为OFF.但是，这可能会在将来的SQLite版本中发生变化.

As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite.

就目前而言，它仍然默认为false.

As for now, this still defaults to false.

CL已经告诉您 foreign_keys 设置是每个连接"-这意味着每次您与SQLite数据库建立新连接时， foreign_keys 编译指示设置为默认值，即 false .

CL already told you that foreign_keys setting is "per connection" - what it means is that every time you make a new connection to the SQLite database, the foreign_keys pragma is set to default value, which is false.

您需要做的是在您与SQLite进行每次连接后执行 PRAGMA foreign_keys = 1; ，因此C#应用程序会使用外键.

What you need to do is to execute PRAGMA foreign_keys = 1; after every connection you make to the SQLite, so foreign keys are honored by your C# application.

这实际上是SQLiteStudio在内部执行的操作，这就是为什么它可以在其中工作的原因.

This is actually what SQLiteStudio does internally and that's why it works there.

这篇关于ON DELETE CASCADE在C#中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！