错误：insert语句与外键约束冲突 [英] Error: the insert statement conflicted with the foreign key constraint

问题描述

我正在尝试将数据插入SQL数据库但是我遇到了一些错误

{

INSERT语句与FOREIGN KEY约束FK_Subject_Course冲突。冲突发生在数据库Course_Semester_Subject，表dbo.Course，列'Course_ID'。

}

I AM TRYING TO INSERT DATA TO SQL DATABASE BUT I GETTING SOME ERROR
{
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Subject_Course". The conflict occurred in database "Course_Semester_Subject", table "dbo.Course", column 'Course_ID'.
}

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=ASHISH;Initial Catalog=Course_Semester_Subject;Integrated Security=True"
        con.Open()
        cmd.Connection = con


        If TextBox1.Text = "" Then
            MessageBox.Show("Please Enter Course ID")
        ElseIf TextBox2.Text = "" Then
            MessageBox.Show("Please Enter Course Name")
        ElseIf ComboBox1.Text = "" Then
            MessageBox.Show("Please Enter Duration")
        ElseIf TextBox3.Text = "" Then
            MessageBox.Show("Please Enter Semester ID")
        ElseIf TextBox4.Text = "" Then
            MessageBox.Show("Please Enter Semester Name")
        ElseIf TextBox5.Text = "" Then
            MessageBox.Show("Please Enter Subject")
        Else
            cmd.CommandText = (("INSERT INTO Course (Course_ID,Course_Name,Duration) VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox1.Text & "')"))
            cmd.CommandText = (("INSERT INTO Semester (Course_ID,Semester_ID,Semester_Name) VALUES('" & TextBox1.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "')"))
            cmd.CommandText = (("INSERT INTO Subject (Course_ID,Semester_ID,Subject) VALUES('" & TextBox1.Text & "','" & TextBox3.Text & "','" & TextBox5.Text & "')"))
            If cmd.ExecuteNonQuery() Then
                MessageBox.Show("Insertation Sucessful")
                ComboBox1.Text = ""
                TextBox1.Text = ""
                TextBox2.Text = ""
            Else
                MessageBox.Show("Error")

            End If
        End If
    End Sub
End Class

推荐答案

几件事：

1）不要那样做！不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

2）存在外键关系以确保数据库保持安全 - 表之间没有缺少链接会导致应用程序混乱。例如，您不能在发票上创建行，除非他们引用的产品都存在，并且发票本身存在以便客户将其发送给！

因此请尝试在其中执行INSERT操作其他顺序：具有外键值的表需要在它们试图引用的行之后插入。

3）当你完成后，你需要执行查询才能发送数据到SQL - 只做一个ExecuteNonQuery只提交最后一个，而不是每一个！

4）如果你正在做多个，相互依赖的INSERT，你真的需要使用一个围绕所有的事务它们与Try-Catch块一起确保只有在数据库全部成功时才会将更改提交给数据库。如果你不这样做，你的数据将最终充满部分插入值，这些值根本不是......

Several things:
1) Don't do that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
2) Foreign key relationships exist to ensure that your database remains "safe" - that there are no "missing links" between tables which will mess up your application. For example, your can't create lines on an invoice unless both the products they reference exist, and the invoice itself exists to have a customer to send it to!
So try doing your INSERT operations in the other order: tables with Foreign Key values need to be INSERTED after the the rows they are trying to reference.
3) When you've done that, you need to execute the query in order to send the data to SQL - just doing a single ExecuteNonQuery will only submit the last one, not each of them!.
4) If you are doing multiple, interdependent, INSERTs you really need to use a Transaction around all of them together with a Try - Catch block to make sure that teh changes are only committed to the DB if they all succeed. If you don't your data will end up full of "partial" insert values which isn't good at all...

你的方法从一开始就是错误的。通过串联从UI获取的字符串组成的查询。不仅重复的字符串连接是低效的（因为字符串是不可变的;我是否必须解释为什么它会使重复连接变坏？），但是有更重要的问题：它打开了通向良好的大门已知的漏洞称为 SQL注入。



这是它的工作原理： http://xkcd.com/327 。



你明白了吗？从控件中获取的字符串可以是任何东西，包括......一段SQL代码。



怎么办？只需阅读有关此问题和主要补救措施：参数化语句： http://en.wikipedia.org/ wiki / SQL_injection 。



使用ADO.NET，使用：http://msdn.microsoft.com/en-us/library/ff648339.aspx 。



请参阅我过去的答案有更多细节：

在com.ExecuteNonQuery中更新EROR（ ）; ，

嗨名字没有显示名称？。



另请参阅Richard Deeming在对该问题的评论中提供的链接。

-SA

Your approach is wrong from the very beginning. The query composed by concatenation with strings taken from UI. Not only repeated string concatenation is inefficient (because strings are immutable; do I have to explain why it makes repeated concatenation bad?), but there is way more important issue: it opens the doors to a well-known exploit called SQL injection.

This is how it works: http://xkcd.com/327.

Are you getting the idea? The string taken from a control can be anything, including… a fragment of SQL code.

What to do? Just read about this problem and the main remedy: parametrized statements: http://en.wikipedia.org/wiki/SQL_injection.

With ADO.NET, use this: http://msdn.microsoft.com/en-us/library/ff648339.aspx.

Please see my past answers for some more detail:
EROR IN UPATE in com.ExecuteNonQuery();,
hi name is not displaying in name?.

See also the links provided by Richard Deeming in his comment to the question.

—SA

这篇关于错误：insert语句与外键约束冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！