I'm trying a simple update of T_ADDRESS to get our addresses in line according to the USPS standards. the problem is I get the following error message:
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_T_ADDRESS_TR_STATE_state". The conflict occurred in database "Impresario", table "dbo.TR_STATE".
While I understand the concept of the foreign key constraint error, I don't understand how specifically changing the state to something else (in the case where people have moved out of state) in an upddate. Do I have to inactivate the current primary address, then add the new address as a new primary address? A little explanation of the error and what can be done about it would be very helpful.
Thanks, Tessiturians.
April Fool's Trivia: Apple Computer was founded on this day in 1976.
Hi Matt
No, it's most likely telling you that the state that you're trying to update the record to doesn't exist in the tr_state table (so it's violating the FK constraint.)
Ken
Matt,
What Ken said.
I would also mention the time wasting problem I have had in the past. Some of the entries in our TR_STATE table had trailing spaces. So when I queried it and saw "KS" as a value I couldn't understand why I wasn't allowed to update T_ADDRESS with "KS". In reality, the TR_STATE record said "KS ".
Thank you, Ken and Levi, for your suggestions.
I ran a couple of queries; First to determine if any of the states (id) in the TR_STATE table have a LEN(id) > 2 where country = 1. It came up with nothing. Second, I did essentially the same query on my local table to see if any of the states I was trying to update had trailing spaces; again, nothing. Then I looked to see if there were any states in my local table that weren’t in my TR_STATE table; there was only one MP (which turns out to be Saipan of the Northern Marianas Islands in the Pacific Ocean!). Even after weeding that entry out, I still get the error! I’m at a loss here.
What I’m thinking of trying is to simply add these address updates as new primary addresses and inactivating the existing primary addresses. But, I wonder if I’ll still run into the same problem…any thoughts?
Thanks, again, everyone.
Having actually looked at the FK now, I see that it's based on country and state, not just state, so it's the combination of country and state that you're trying to update to that needs to be present in tr_state.
The other thing to check is whether you're trying to put a null value in one of those guys.
I'm sorry to say that I've followed through on all of your helpful suggestions and still no headway. I'm not sure what the hell this message really means and I am considering opening a ticket, even though I feel kind of silly for doing so. It's not like the system isn't working, it's just that I don't know exactly what it's telling me so I can solve the issue and get this import done.
I've followed up on a suspicion that perhaps the state I was updating to was causing problems in that I was not updating country, and if I was changing the state to one in a different country than the one I was replacing, but not also updating the country, I could see the system saying "oh teh noes!", but that did not appear to be the case, so I am truly stumped at this point.
I feel like a fool. A big, handsome fool.
Thanks again for your help, Ken and Levi. I'll be sure to pass on whatever I end up learning on this issue.
Have you tried running this sort of query to check explicitly whether anything at all in your local table isn't in the tr_state key?
-----
select m.*
from
my_table m
on
m.country = s.country and m.state = s.id
where
s.id is null
-----------
- And maybe you should post your update statement here and see if another pair of eyes can spot some deviance from the True SQL Way?
Of course, I should have put the query here in the first place. Sorry about that.
update aset a.street1 = t.street1,a.street2 = t.street2,a.city = t.city,a.state = t.state,a.postal_code = t.postal_codefrom MG_T_ADDRESS_TEMP_LOCAL tjoin t_address a on a.customer_no = t.customer_nowhere a.primary_ind = 'Y' and a.inactive = 'N'
MG_T_ADDRESS_TEMP_LOCAL is the table with the imported address data and it does not have a country column in it. I'm trying to write over everyone's primary address with the new data (not my idea, but there you go).
Thanks again for your help, guys.
Sorry to have to say this, but I can't see anything wrong with your script. In fact I ran it on our test db, and it worked fine and dandy. Only way i could get the error was by putting in a state in a source table record that didn't exist for the country on the existing record.
So your theory is fine. Must be something about the data that you're trying to update with that is causing a mismatch.