不能删除，因为限制数据库 [英] Cant delete in database because of constraints

问题描述

我作出asp.net项目，我想删除我的数据库中的一行，但该表有限制。我用这个方法来删除我it.How可以重写它，以便所有约束都是为了我能正确地从表中删除行禁用或东西。

 公共BOOL DeleteMovie（INT MovieID）
    {
        字符串SQL =DELETE FROM DBS2_MOVIE WHERE MOVIE_ID =：帐户ID
        尝试
        {            this.Connect（）;
            CMD的OracleCommand =新的OracleCommand（SQL，this.connection）;
            cmd.Parameters.Add（新的OracleParameter（帐户ID，MovieID））;
            OracleDataReader DATAREAD = cmd.ExecuteReader（）;
        }
        赶上（例外五）
        {
            Console.WriteLine（e.Message）;
            扔;
        }
        最后
        {
            this.connection.Close（）;
        }        返回true;
    }
 

解决方案

要做到这一点的最佳方法是使用一个存储进行，而不是在C＃code的SQL语句。你得到的错误，因为引用的记录仍然是美元的引用表P $ psent并使用 cmd.ExecuteReader（）; ，而不是 cmd.ExecuteNonQuery （）; 。所以，你需要删除记录 DBS2_MOVIE WHERE MOVIE_ID =：从所有表帐户ID 哪里有约束的表DBS2_MOVIE是present

继code可以帮助你：

CREATE OR REPLACE PROCEDURE Delete_DBS2_MOVIE（p_MOVIE_ID IN DBS2_MOVIE.MOVIE_ID％TYPE）
IS
开始  --first从所有表中删除p_MOVIE_ID那里是DBS2_MOVIE约束
  --i.e。所有的约束您收到错误
  DELETE FROM xxx其中MOVIE_ID = p_MOVIE_ID  DELETE FROM DBS2_MOVIE WHERE MOVIE_ID = p_MOVIE_ID;结束;

修改你的C＃code为：

 公共BOOL DeleteMovie（INT MovieID）
{
    字符串SQL =Delete_DBS2_MOVIE;
    尝试
    {        this.Connect（）;
        CMD的OracleCommand =新的OracleCommand（SQL，this.connection）;        cmd.Parameters.Add（新的OracleParameter（p_MOVIE_ID，MovieID））;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;        cmd.ExecuteNonQuery（）;
    }
    赶上（例外五）
    {
        Console.WriteLine（e.Message）;
        扔;
    }
    最后
    {
        this.connection.Close（）;
    }    返回true;
}
 

I am making an asp.net project and I am trying to delete a row in my database but that table got constraints. I am using this method to delete it.How can I rewrite it so that all constraints are disabled or something in order that i can properly delete a row from the table.

  public bool DeleteMovie(int MovieID)
    {


        string sql = "DELETE FROM DBS2_MOVIE WHERE MOVIE_ID = :AccountID ";
        try
        {

            this.Connect();
            OracleCommand cmd = new OracleCommand(sql, this.connection);
            cmd.Parameters.Add(new OracleParameter("AccountID", MovieID));


            OracleDataReader DataRead = cmd.ExecuteReader();


        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            throw;
        }
        finally
        {
            this.connection.Close();
        }

        return true;
    }

解决方案

Best way to do it is by using a stored proceed rather than a sql statement in C# code. You are getting error because the referenced records are still present in referenced table and are using cmd.ExecuteReader(); rather than cmd.ExecuteNonQuery();. So you need to delete records for DBS2_MOVIE WHERE MOVIE_ID = :AccountID from all the tables where there is constraint for table DBS2_MOVIE is present.

Following code may help you:

CREATE OR REPLACE PROCEDURE Delete_DBS2_MOVIE(p_MOVIE_ID IN DBS2_MOVIE.MOVIE_ID%TYPE)
IS
BEGIN

  --First delete p_MOVIE_ID from all the table where there is a constraint on DBS2_MOVIE 
  --i.e. all the constraints you are getting error for
  DELETE FROM XXX WHERE MOVIE_ID = p_MOVIE_ID

  DELETE FROM DBS2_MOVIE WHERE MOVIE_ID = p_MOVIE_ID;

END;

Modify your C# code as:

public bool DeleteMovie(int MovieID)
{


    string sql = "Delete_DBS2_MOVIE";
    try
    {

        this.Connect();
        OracleCommand cmd = new OracleCommand(sql, this.connection);

        cmd.Parameters.Add(new OracleParameter("p_MOVIE_ID", MovieID));
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.ExecuteNonQuery();


    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
        throw;
    }
    finally
    {
        this.connection.Close();
    }

    return true;
}

这篇关于不能删除，因为限制数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！