I have three tables in SQL Server 2008 R2: Country Address and Country_Address
我在SQL Server 2008 R2中有三个表:国家地址和Country_Address
Country has columns:
国家列:
CountryID Country
CountryID国家
1, Afghanistan
1、阿富汗
2, Australia
2、澳大利亚
Address has columns:
地址已经列:
AddressID Address
AddressID地址
1, 5 Smith Way
1,史密斯5
Country_Address has columns:
Country_Address列:
CountryID AddressID
CountryID AddressID
1, 1
1,- 1
edit: so in the example, 5 smith way is an address for Afghanistan.
编辑:在这个例子中，5史密斯路是阿富汗的一个地址。
I have emptied the tables, then added two countries and one address, and then a link in Country_Address between 1 and 1. When I try to update the CountryID from 1 to 2, I can't because of FK constraint. I don't understand why this is, because the CountryID I am trying to assign does exist. So how do I manage to update it?
我清空了表格，然后添加了两个国家和一个地址，然后在Country_Address中添加了一个链接，在1和1之间。当我试图将CountryID从1更新到2时，由于FK约束，我不能这样做。我不明白为什么会这样，因为我要分配的国家确实存在。那么我如何更新它呢?
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.
这是我在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'.
第1行中的数据没有提交。错误源:。net SqlClient数据提供程序。错误消息:更新语句与外键约束“FK_Country_Address_Address”冲突。冲突发生在数据库“…”中。dbo、表”。地址”,列“AddressID”。
The statement has been terminated.
声明已被终止。
1 个解决方案
#1
5
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_Address表将包含对国家表的无效引用。
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_Address are reversed, or at least incorrect on the CountryID column. Please verify the foreign key definitions.
编辑:如果我最终理解正确，我认为Country_Address中的外键是颠倒的，或者至少在CountryID列上是不正确的。请验证外键定义。
Doing UPDATE Country_Address SET CountryID = 2 should work if everything is set up correctly. The error message you're getting when attempting to update should never cause an invalid value in an AddressID column -- this indicates to me that the foreign key is set up incorrectly.
如果一切设置正确，那么执行UPDATE Country_Address SET CountryID = 2应该可以工作。在尝试更新时所得到的错误消息不应该在AddressID列中造成无效的值，这表明外键是错误设置的。
#1
5
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_Address表将包含对国家表的无效引用。
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_Address are reversed, or at least incorrect on the CountryID column. Please verify the foreign key definitions.
编辑:如果我最终理解正确，我认为Country_Address中的外键是颠倒的，或者至少在CountryID列上是不正确的。请验证外键定义。
Doing UPDATE Country_Address SET CountryID = 2 should work if everything is set up correctly. The error message you're getting when attempting to update should never cause an invalid value in an AddressID column -- this indicates to me that the foreign key is set up incorrectly.
如果一切设置正确，那么执行UPDATE Country_Address SET CountryID = 2应该可以工作。在尝试更新时所得到的错误消息不应该在AddressID列中造成无效的值，这表明外键是错误设置的。