System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 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:
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:
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:
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();
                }
            }
        }
    }
}