Why I cannot delete Database column programmatically?-CodePudding

According to my question here How to update Database columns by object? I found new problem connected with this question(all details are here like SQL Scheme) and maybe thanks this I will found the solution for my older question also. My problem here is that I cannot delete Database column. It throws SqlException: 'The DELETE statement conflicted with the REFERENCE constraint . That means I can't remove the Problem from the database when the Alert also exists. So what I am missing in Delete methods, it seems to me okay? Debugging also showed nothing suspicious.

Problem Delete method

  public void Delete(Problem element)
        {
            using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand("DELETE FROM Problem WHERE id = @id", conn))
                {
                    command.Parameters.Add(new SqlParameter("@id", element.Id));
                    command.ExecuteNonQuery();
                    MessageBox.Show("Problem DELETED");
                }
                conn.Close();
            }

Alert Delete method

public void Delete(Alert element)
        {

            using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
            {
                conn.Open();

                using (SqlCommand command = new SqlCommand("DELETE FROM [Alert] WHERE id = @id", conn))
                {
                    command.Parameters.AddWithValue("@id" , element.Id);
                    command.ExecuteNonQuery();
                    MessageBox.Show("Alert DELETED");
                }
                conn.Close();
            }

           
        }

SQL Scheme

CREATE TABLE Problem(
id int primary key identity(1,1) not null,
nameOfAlert varchar(50) not null,
Value_ID  int FOREIGN KEY REFERENCES [Value](id) not null,
Result_ID int FOREIGN KEY REFERENCES [Problem](id) not null,
message_ID varchar(100) not null
);


CREATE TABLE [Alert](
id int primary key identity(1,1) not null,
message_ID varchar(100) not null,
[date] datetime not null,
element varchar(50) not null,
AMUser_ID  int FOREIGN KEY REFERENCES [AMUser](id) not null,
Problem_ID  int FOREIGN KEY REFERENCES [Problem](id) not null,
clearTime int
);

CodePudding user response：

You have a self-referencing Problem table. This means that you need to delete a Problem along with all its child Problem rows at the same time.

You can do this using a recursive CTE.

Alert is also dependent on Problem, so you need to delete Alert rows dependent on all related Problem rows.

public void Delete(Problem element)
{
    const string query = @"
WITH cte AS (
    SELECT id = @id, FirstId = @id

    UNION ALL

    SELECT p.id, cte.FirstId
    FROM Problem p
    JOIN cte ON p.Result_ID = cte.id AND p.Id <> p.FirstId
)
DELETE a
FROM Alert a
JOIN cte ON a.Problem_ID = cte.id;

WITH cte AS (
    SELECT id = @id, FirstId = @id

    UNION ALL

    SELECT p.id, cte.FirstId
    FROM Problem p
    JOIN cte ON p.Result_ID = cte.id AND p.Id <> p.FirstId
)
DELETE p
FROM Problem p
JOIN cte ON cte.id = p.id;
";
    using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
    using (SqlCommand command = new SqlCommand(query, conn))
    {
        command.Parameters.Add("@id", SqlDbtype.Int).Value = element.Id;  // always specify type
        conn.Open();
        command.ExecuteNonQuery();
    }  // close connection before showing message

    MessageBox.Show("Problem DELETED");
}