SQL 为什么我不能更新这个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4342517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why can't I update this table?
提问by baron
I have three tables in SQL Server 2008 R2: CountryAddressand Country_Address
我在 SQL Server 2008 R2 中有三个表：CountryAddress和Country_Address
Countryhas columns:
Country有列：
CountryIDCountry
CountryIDCountry
1, Afghanistan
1、阿富汗
2, Australia
2、澳大利亚
Addresshas columns:
Address有列：
AddressIDAddress
AddressIDAddress
1, 5 Smith Way
1, 5 史密斯路
Country_Addresshas columns:
Country_Address有列：
CountryIDAddressID
CountryIDAddressID
1, 1
1, 1
edit: so in the example, 5 smith way is an address for Afghanistan.
编辑：所以在这个例子中，5 smith way 是阿富汗的地址。
I have emptied the tables, then added two countries and one address, and then a link in Country_Addressbetween 1 and 1. When I try to update the CountryIDfrom 1 to 2, I can't because of FK constraint. I don't understand why this is, because the CountryIDI am trying to assign does exist. So how do I manage to update it?
我清空了表格，然后添加了两个国家/地区和一个地址，然后添加了Country_Address1 和 1 之间的链接。当我尝试将CountryID1更新到 2 时，由于 FK 约束，我不能。我不明白这是为什么，因为CountryID我试图分配的确实存在。那么我如何设法更新它？
edit1: This is the error I get when I try to do the update in SQL Management Studio, I get InvalidOperationException- "The model of type '...' could not be updated." in Visual Studio.
edit1：这是我尝试在 SQL Management Studio 中进行更新时遇到的错误，我得到InvalidOperationException- “无法更新类型 '...' 的模型。” 在 Visual Studio 中。
No row was updated.
The data in row 1 was not committed. Error Source: .Net SqlClient Data Provider. Error Message: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Country_Address_Address". The conflict occurred in database "...", table "dbo.Address", column 'AddressID'.
The statement has been terminated.
没有更新任何行。
第 1 行中的数据未提交。错误来源：.Net SqlClient 数据提供程序。错误消息：UPDATE 语句与 FOREIGN KEY 约束“FK_Country_Address_Address”冲突。冲突发生在数据库“...”、表“dbo.Address”、“AddressID”列中。
该语句已终止。
回答by Jon Seigel
The problem is that when you try to update the primary key value in the Countries table, the Country_Address table would then contain an invalid reference back to the Countries table.
问题是当您尝试更新 Country 表中的主键值时，Country_Address 表将包含对 Country 表的无效引用。
If you want to update the keys like this, the easiest way is to enable what are called cascading updates. You do this when creating the foreign keys themselves.
如果您想像这样更新密钥，最简单的方法是启用所谓的级联更新。您在自己创建外键时执行此操作。
See here: http://msdn.microsoft.com/en-us/library/aa933119%28SQL.80%29.aspx
请参见此处：http: //msdn.microsoft.com/en-us/library/aa933119%28SQL.80%29.aspx
EDIT:If I'm finally understanding this correctly, I think the foreign keys in Country_Addressare reversed, or at least incorrect on the CountryIDcolumn. Please verify the foreign key definitions.
编辑：如果我最终正确理解了这一点，我认为中的外键Country_Address是颠倒的，或者至少在CountryID列上是不正确的。请验证外键定义。
Doing UPDATE Country_Address SET CountryID = 2shouldwork if everything is set up correctly. The error message you're getting when attempting to update should nevercause an invalid value in an AddressIDcolumn -- this indicates to me that the foreign key is set up incorrectly.
如果一切都设置正确，这样做UPDATE Country_Address SET CountryID = 2应该可以工作。试图更新时，应你得到该错误消息从未造成的无效值AddressID列-这表明，我认为外键设置不正确。
回答by Komal Guram
In SQL server, Tools tab -> Options-> Designer -> unselect the Prevent from making changes option. Then can directly edit table. If after adding row data, get error "no row was updated". Then select "New Query" then write insert query for a particular table. That's it It worked for me ,hope it might help
在 SQL Server 中，工具选项卡 -> 选项 -> 设计器 -> 取消选择阻止更改选项。然后就可以直接编辑表格了。如果添加行数据后，得到错误“没有行被更新”。然后选择“新建查询”，然后为特定表编写插入查询。就是这样它对我有用，希望它可以帮助
