2

There are two tables [UserData] and [HotelData] I've linked them with a foreign key. which is "Username" and I want to delete which ever Username is entered and delete its data on the 2nd table as well. I don't know how to write the sql command or c#.

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: The DELETE statement conflicted with the REFERENCE constraint "FKHotelData". The conflict occurred in database "E:\GRADED UNIT DEV\BLACKMARCH\BLACKMARCH\BIN\DEBUG\DATABASEBM.MDF", table "dbo.HotelData", column 'Username'.

The statement has been terminated.

private void btnDelete_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\Graded unit Dev\BlackMarch\BlackMarch\bin\Debug\DataBaseBM.mdf;Integrated Security=True;Connect Timeout=30");
    string sqlStatement = "DELETE FROM UserData WHERE Username = @Username";
    con.Open();
    SqlCommand cmd = new SqlCommand(sqlStatement, con);
    cmd.Parameters.AddWithValue("@Username", txtUsernameUser.Text);
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();
    con.Close();
}
Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
aalloo
  • 27
  • 4
  • 1
    Delete from the HotelData first, and from UserDate afterwards. – Abdullah Nehir May 26 '16 at 11:04
  • the message is quite explanatory. Since you have a FK constraint you cant delete entry from UserData unless you 1st delete the associated entries from HotelData – apomene May 26 '16 at 11:04
  • can you show your table structure and model – anand May 26 '16 at 11:09
  • Either delete sequentionally, the "child" row(s) and then "parent row", or enable cascade delete – Alex May 26 '16 at 11:41
  • Hi. If you feel an answer solved the problem, please mark it as 'accepted' by clicking the gray check mark beside the answer. Check this link to know how does accepting an answer work:http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Salah Akbari Jun 05 '16 at 09:43

3 Answers3

2

There is no need to write any code to delete data on the child table when you delete a record from the UserData table. Just use Cascade delete behavior. It means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.

Do the following steps:

  1. Open the child table's designer and click Relationships
  2. In the opened window and in the Table Designer expand INSERT And UPDATE Specification
  3. Change the Delete Rule from No action to Cascade

Like the following image:

enter image description here

Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
1

The DELETE statement conflicted with the REFERENCE constraint "FKHotelData"

You have a constraint and this is helping with performance, do yourself a favor and add a column IsDeleted with a default value of 0 or to allow null's so it doesn't affect anything.

Then change your SELECT queries to have a WHERE clause that includes the condition

WHERE IsDeleted = 0

The point here is that its a Hotel business and they will want metrics/reporting on bookings. For medical and confidentiality purposes I'd lean toward the cascading delete answers. For this scenario it would be better to logically delete the data instead of physically delete it.

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
0

Well. To expand on the other option which isn't exactly ideal, but should be available as well. CASCADE is easier to work with though.

Without that, your best bet is 

DELETE FROM HotelData WHERE Username = @Username

Followed by 

DELETE FROM UserData WHERE Username = @Username
Sava Glodic
  • 103
  • 2
  • 6