MSSQL Delete query on a table with foreign key

Question

I have 2 tables

t1 - Categories Id Name

t2 - Products Id Name CategoryId

I have a foreign key and it`s working but when I want to run a Delete query (for example O keep getting an error because of the foreign key.)

ths is my code:

        protected void dlCategory_DeleteCommand(object source, DataListCommandEventArgs e)
{
    string Id = ((Label)e.Item.FindControl("Label1")).Text;
    string SQL = "DELETE FROM Categories WHERE PrimaryKey=@ID;";
    SqlCommand cmd = new SqlCommand(SQL, conn);
    cmd.Parameters.AddWithValue("@ID", Id);

    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();

    BindDL();
}

If I remove the foreign key it`s working just fine!

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

As far as I'm aware you can't run a query with the Primary Key as a parameter, You need to do the long handed way.

string SQL = "DELETE FROM Categories WHERE PrimaryKey= '$Cat_ID';";

Similar answer here Mysql Select record where PRIMARY key = x

edited May 23 '17 at 12:15

Community

1
1

answered Nov 10 '15 at 11:31

TomJ

424
3
14

score 0 · Answer 2 · answered Nov 10 '15 at 11:33

0

It looks like you are trying to remove a record from Categories, but that record cannot be deleted because it is in use by the Products table.

If you have a category of 'clothes' and a product of 't-shirt' that is given the category of clothes, then removing the category would give you a data error, as the database is now inconsistent.

This is expected functionality. You can amend SQL Server to use cascading deletes, so all connected records are also deleted, but use this carefully.

answered Nov 10 '15 at 11:33

Karl Gjertsen

4,690
8
41
64

I created a new Category, it`s not used by any Product. Anyway, can you give me an example of how cascading deletes are working ?! Thanks – Peter Nov 10 '15 at 11:52
Are both tables empty, apart from the record you are trying to delete? – Karl Gjertsen Nov 10 '15 at 11:53
Cascading deletes would delete any products that have the Category ID that is being deleted from the Category table. These are set in the database relationship between the tables. In the properties, under the 'INSERT And UPDATE Specification' section, set the 'Delete Rule' to 'Cascade'. – Karl Gjertsen Nov 10 '15 at 12:03

MSSQL Delete query on a table with foreign key

2 Answers2