0

I've got a method that deletes all data in a table however this wont work if there is a foreign key constraint. How do I check if there any reference constraints exist, delete those entriesfirst and then proceed to deleting all data in the specified table?

This is the exception thrown if there are any foreign key constraints:

"The DELETE statement conflicted with the REFERENCE constraint \"". The conflict occurred in database \"\", table \"\", column ''.\r\nThe statement has been terminated."}

Method:

public int DeleteFromDatabase(SqlConnection sqlConnection, string tableName)
{
    int success = 0;

    string sqlTrunc = "Delete from " + tableName;

    if (isSafeSqlConnection(sqlConnection))
    {
        using (sqlConnection)
        {
            SqlCommand cmd = new SqlCommand(sqlTrunc, sqlConnection);
            sqlConnection.Open();
            success = cmd.ExecuteNonQuery(); //<-exception when constraint exists.
            sqlConnection.Close();
        }
    }
    return success;
}

What I need to know how to do is either: Drop constraint, delete constraint, reinstate constraint OR the better method being delete referenced constrainted data first and then proceed to deleting the table. Either way is ok for me.

Stefan
  • 652
  • 5
  • 19
  • http://stackoverflow.com/questions/3892345/sql-server-truncate-table-drop-and-recreate-fk-constraints-script – calinaadi Nov 05 '15 at 15:51

1 Answers1

0

You have a couple of options:

  1. Set your table to cascade deletes in your database diagram in SQL Server Management Studio. enter image description here
  2. List all of the keys on your table: EXEC sp_fkeys 'MyTable' Then delete all of the rows in that foreign key table first. See How can I list all foreign keys referencing a given table in SQL Server?
Community
  • 1
  • 1
user8128167
  • 6,929
  • 6
  • 66
  • 79
  • How is it possible to do this through C#? you cannot use DataReader to read data that is not in an entry. –  Nov 06 '15 at 11:43
  • e.g. If I run that query through SqlCommand i cannot take the output as DataReader only works with data thats actually in the db, i think. –  Nov 06 '15 at 11:43