How to delete related data from child table when I delete specific data from parent table in DB usin-CodePudding

I have many tables in this database. Two of them being Student and StudentAttendance. In Student table, Id is the primary key which is auto-generated and in the StudentAttendance table AttendanceId is the primary key and StudentId is foreign key. Now the requirement is that If I delete any student by giving its registration number in the textbox and then clicking the delete button then student record with this id should also get deleted from the StudentAttendance table. I cannot do this with CASCADE DELETE. I have to do this with a query.
I am trying this code but this is giving me error and not working.

private void studentDeleteButton_Click(object sender, EventArgs e)
        {
            var con = Configuration.getInstance().getConnection();
            SqlCommand cmd = new SqlCommand("Delete from Student Where RegistrationNumber = @RegistrationNumber", con);
            cmd.Parameters.AddWithValue("@RegistrationNumber", addStudentRegTextbox.Text);
            cmd.ExecuteNonQuery();
            SqlCommand cmd1 = new SqlCommand("Delete from StudentAttendance Where StudentId = id", con);
            cmd1.ExecuteNonQuery();
            MessageBox.Show("Record has been Deleted successfully!");

}

CodePudding user response：

Firstly, check and make sure your mysql database uses InnoDB engine;
Then, check if you have set the physical foreign-key relation. Theoretically you should add this sql sentence when you create your child table:

ADD CONSTRAINT `atten_stu_fk` FOREIGN KEY (`StudentId`) REFERENCES `student` (`id`) ON DELETE CASCADE;

Maintain the foreign key relation by the code:

private void studentDeleteButton_Click(object sender, EventArgs e) {
            var con = Configuration.getInstance().getConnection();
            SqlCommand cmd = new SqlCommand("Delete from StudentAttendance Where StudentId = id", con);
            cmd.ExecuteNonQuery();
            SqlCommand cmd1 = new SqlCommand("Delete from Student Where RegistrationNumber = @RegistrationNumber", con);
            cmd1.Parameters.AddWithValue("@RegistrationNumber", addStudentRegTextbox.Text);
            cmd1.ExecuteNonQuery();
            MessageBox.Show("Record has been Deleted successfully!");
  }

CodePudding user response：

yur code should work fine but you are missing the "@" for the id in the second delete and you can add parametrised to your sql to avoid sql injection

 private void studentDeleteButton_Click(object sender, EventArgs e)
                {
                    var con = Configuration.getInstance().getConnection();
                    SqlCommand cmd = new SqlCommand("Delete from Student Where RegistrationNumber = @RegistrationNumber", con);
                    cmd.Parameters.AddWithValue("@RegistrationNumber", addStudentRegTextbox.Text);
                    cmd.ExecuteNonQuery();
                    SqlCommand cmd1 = new SqlCommand("Delete from StudentAttendance Where StudentId = @id", con);
                    cmd1.Parameters.AddWithValue("@id", addStudentRegTextbox.Text);
                    cmd1.ExecuteNonQuery();
                    MessageBox.Show("Record has been Deleted successfully!");