如何删除包含数据的行及其父表在另一个表中 [英] How to delete a row with data with its parent row in another table

问题描述

有两个表[ UserData ]和[ HotelData ]我已将它们与外键链接在一起。这是 用户名，我想删除输入的用户名，并删除其在第二个表上的数据。我不知道如何编写sql命令或c＃。

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#.

类型为'System.Data.SqlClient.SqlException'的未处理异常
发生在System.Data.dll

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

其他信息：DELETE语句与
REFERENCE约束 FKHotelData冲突。数据库
E：\GRADED UNIT DEV\B​​LACKMARCH\BLACKMARCH\BIN\DEBUG\DATABASEBM.MDF，
表 dbo.HotelData的用户名列中发生了冲突'。

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'.

该语句已终止。

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

推荐答案

无需编写从 UserData 表中删除记录时删除子表上数据的任何代码。只需使用 Cascade 删除行为即可。这意味着，如果删除父表中的记录，则子表中的相应记录将被自动删除。

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.

执行以下步骤：

1. 打开子表的设计器，然后单击关系


2. 在打开的窗口中并在表设计器展开INSERT和UPDATE规范


3. 将删除规则从无操作更改为 Cascade

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

就像下面的图片一样：

这篇关于如何删除包含数据的行及其父表在另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！