Why can't I update this table?

I have three tables in SQL Server 2008 R2: Country Address and Country_Address

Country has columns:

CountryID Country

1, Afghanistan

2, Australia

Address has columns:

AddressID Address

1, 5 Smith Way

Country_Address has columns:

CountryID AddressID

1, 1

edit: so in the example, 5 smith way is an address for Afghanistan.

I have emptied the tables, then added two countries and one address, and then a link in Countr开发者_StackOverflow社区y_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?

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.

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.

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.

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

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.

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.

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