I want to truncate a table which is referenced through foreign key constraints.

    private int clean_table(string table)
    {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(CadConexion))
                {
                    conn.Open();

                    String sql = "TRUNCATE TABLE mydb."   table;

                using (MySqlCommand comm = new MySqlCommand(sql, conn))
                    {
                        comm.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                log.Error("clean_table "   e.Message);
            return 0;
            }

        return 1;
    } 

This will return:

Cannot truncate a table referenced in a foreign key constraint

So, in mySql i would solve it with:

SET FOREIGN_KEY_CHECKS=0

But running a MySqlCommand with it seems not to be working.

  String sql = "SET FOREIGN_KEY_CHECKS=0";

            using (MySqlCommand comm = new MySqlCommand(sql, conn))
                {
                    comm.ExecuteNonQuery();
                }

I check it with:

  SELECT @@GLOBAL.foreign_key_checks, @@SESSION.foreign_key_checks;

How can i achieve the same result?

P.D: I know i could go for a workaround of making a copy of tables with key constraints and delete them and afterwards restore them, delete key constraints and restore them etc. but i would prefer to make it simple. Also deleting the table instead of truncating would not be an option, as it would delete registries of other tables on cascade, and i would need to backup-restore them.

CodePudding user response：

Ok, i found a way which seems to work:

private int clean_table(string table)
{
        try
        {
            using (MySqlConnection conn = new MySqlConnection(CadConexion))
            {
                conn.Open();

                String sql = "SET FOREIGN_KEY_CHECKS=0; TRUNCATE TABLE mydb."   table;

            using (MySqlCommand comm = new MySqlCommand(sql, conn))
                {
                    comm.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
        catch (Exception e)
        {
            log.Error("clean_table "   e.Message);
        return 0;
        }

    return 1;
} 

Seems like each time you open a connection, you are setting a new session, so if you use both queries in separate connections it will not work.

If you add "SET FOREIGN_KEY_CHECKS=0;" before the truncate, it will be affected.