Why I cannot delete Database column programmatically?

Question

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
);

Have you checked if those tables have foreign-key relations? — Helio, Aug 31 '22 at 12:23
"That means I can't remove the Problem from the database when the Alert also exists." So.... delete the Alert entry as well? Clearly you have a foreign key in your database, but I don't know what that looks like. I don't understand what behavior you want your program to have. — gunr2171, Aug 31 '22 at 12:23
You cant delete it because it is referenced by a key/reference. SO you would have to remove the reference first. This is possible with using system tables to look up those references and remove them but the code would be much more involved and probably better suited to a Stored Procedure. — Brad, Aug 31 '22 at 12:24
Take a look at this question: https://stackoverflow.com/questions/3776269/i-got-error-the-delete-statement-conflicted-with-the-reference-constraint — Elliveny, Aug 31 '22 at 12:24
You all have right, just please look at my previous question where I have SQL Scheme. I know I have to first delete Alert and then Problem because between these 2 table is `Foreign-key relation`. My problem is that both method apparently does not delete columns in Database — Petr, Aug 31 '22 at 12:29
@Charlieface I don't know, the method looks fine, but the row is not removed. That's what I'm asking — Petr, Aug 31 '22 at 12:35
OK we need a [mcve]. Please show us the full table definitions, *including* all primary, unique and foreign keys. Like I said, you'd probably need to delete `Alert` *first* and *then* `Problem` — Charlieface, Aug 31 '22 at 12:39

Charlieface · Accepted Answer · 2022-08-31T14:17:55.927

1

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");
}

edited Aug 31 '22 at 14:17

answered Aug 31 '22 at 12:51

Charlieface

52,284
6
19
43

I should delete `Alert` with the same way with `CTE`? – Petr Aug 31 '22 at 12:58
It works in `MS Studio`, but in it VS still throws the exception even after your great answer – Petr Aug 31 '22 at 13:04
What error is it throwing? Full error message please, with table names and foreign key names – Charlieface Aug 31 '22 at 13:05
`System.Data.SqlClient.SqlException: 'The DELETE statement conflicted with the REFERENCE constraint "FK__Alert__Problem_I__44CA3770". The conflict occurred in database "SmartOne", table "dbo.Alert", column 'Problem_ID'. The statement has been terminated.' ` @Charlieface – Petr Aug 31 '22 at 13:07
OK you need to delete all dependent `Alert` rows also, see edit – Charlieface Aug 31 '22 at 13:11
Hi I know I have closed this question, but can I have another? Very similiar.... – Petr Aug 31 '22 at 13:57
I have already did sir here https://stackoverflow.com/questions/73557236/why-i-cannot-update-or-delete-database-table-with-fk-relations – Petr Aug 31 '22 at 14:11

Why I cannot delete Database column programmatically?

1 Answers1