Ticket Hash: | 4e677c6d196ce186b392dca74ec8f10582aad42c | |||
Title: | foreing key not working in transaction | |||
Status: | Closed | Type: | Incident | |
Severity: | Important | Priority: | Medium | |
Subsystem: | Transaction | Resolution: | Works_As_Designed | |
Last Modified: | 2018-03-08 16:36:31 | |||
Version Found In: | 1.0.108 (3.22) | |||
User Comments: | ||||
anonymous added on 2018-03-07 16:42:49:
Hi, I have issue deleting row with foreign keys. My tables: CREATE TABLE [cliente] ( [idcliente] nvarchar(10) NOT NULL PRIMARY KEY, [descrizione] nvarchar(254) ); CREATE TABLE [ambiente] ( [idcliente] nvarchar(10) NOT NULL, [idambiente] nvarchar(10) NOT NULL, [descrizione] nvarchar(254), [tipo] int DEFAULT 0, CONSTRAINT [sqlite_autoindex_ambiente_1] PRIMARY KEY ([idcliente], [idambiente]), FOREIGN KEY ([idcliente]) REFERENCES [cliente] ([idcliente]) ON UPDATE NO ACTION ON DELETE CASCADE ); I have the following c# code: using (DbConnection cn = this.CreateConnection(string.Format(@"Data Source={0};PRAGMA foreign_keys=ON;", f.FullName))) { DbTransaction transacton = null; try { cn.Open(); transacton = cn.BeginTransaction(); string query = @"PRAGMA foreign_keys=ON; DELETE FROM cliente WHERE idcliente = '{0}';"; int righe = 0; using (DbCommand cmd = cn.CreateCommand()) { cmd.CommandText = string.Format(query, ID_Cliente); righe = cmd.ExecuteNonQuery(); transacton.Commit(); return righe; } } catch (Exception ex) { if (transacton != null) transacton.Rollback(); Log4NetAP.logError(ex.ToString()); throw ex; } finally { cn.Close(); } } when I do "delete from cliente where idcliente=<value>" the referenced row in table ambiente is not deleted. However, where I remove DbTransaction commands everything works as expected: using (DbConnection cn = this.CreateConnection(string.Format(@"Data Source={0};PRAGMA foreign_keys=ON;", f.FullName))) { try { cn.Open(); string query = @"PRAGMA foreign_keys=ON; DELETE FROM cliente WHERE idcliente = '{0}';"; int righe = 0; using (DbCommand cmd = cn.CreateCommand()) { cmd.CommandText = string.Format(query, ID_Cliente); righe = cmd.ExecuteNonQuery(); return righe; } } catch (Exception ex) { Log4NetAP.logError(ex.ToString()); throw ex; } finally { cn.Close(); } } mistachkin added on 2018-03-07 20:09:39: (text/x-fossil-plain) The connection string cannot include PRAGMA statements. If you wish to enable foreign keys, you will want to use something like: string.Format(@"Data Source={0};Foreign Keys=true;", f.FullName) mistachkin added on 2018-03-08 16:34:54: (text/x-fossil-plain) Follow up: "PRAGMA foreign_keys" is documented to be a no-op within a transaction. That is the root cause of this issue. Please use the connection string property mentioned in the previous comment and everything should work. mistachkin added on 2018-03-08 16:36:31: (text/x-fossil-plain) Modified example code: using System; using System.Data.Common; using System.Data.SQLite; namespace ConsoleApplication1 { class Program { static void Main(string[] args) { using (DbConnection cn = new SQLiteConnection("Data Source=test.db;Foreign Keys=true;")) { DbTransaction transacton = null; try { cn.Open(); transacton = cn.BeginTransaction(); string query = @"DELETE FROM cliente WHERE idcliente = '{0}';"; int righe = 0; using (DbCommand cmd = cn.CreateCommand()) { cmd.CommandText = string.Format(query, "one"); righe = cmd.ExecuteNonQuery(); transacton.Commit(); } } catch (Exception ex) { if (transacton != null) transacton.Rollback(); Console.WriteLine(ex.ToString()); throw ex; } finally { cn.Close(); } } } } } |