X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Can't Figure Out How To Cascade Updates, SQL Server 2012    
post Jun 12 2018, 07:33 PM

Posts: 300
Joined: 4-April 12
From: Bendigo, Australia

I'm having trouble figuring out how to cascade updates when I change a foreign key field.

I have imported two tables:

MethodID text PK
MethodDesc text

Field SampID identity PK
other stuff...
MethodID text FK from SampMethod

I know it might be less than ideal to use a text PK, but there are only about a dozen records in SampMethod and it is easier to import the FieldSamp records with the existing FK.

In my development database, I populated the tables, but now I want to change one of the MethodIDs. I tried using SqlServer Management studio to set the relationship's Update Rule to "Cascade". I also tried to set "Enforce Foreign Key Constraint to "No".

Tried to run the SQL:

Update dbo.SampMethod set MethodID = 'flow' where MethodID = 'f';

and get the error:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "FK_FieldSamp_SampMethod". The conflict occurred in database "ARgroundwater", table "dbo.FieldSamp", column 'SampMethod'.
The statement has been terminated.

Can someone show me where I messed up? I'm trying to set up to load data into the production database and don't really like the idea of deleting the relationship to do it.
Go to the top of the page
post Jun 15 2018, 07:34 AM

Posts: 38
Joined: 29-October 02

One or more of your records in FieldSamp has a MethodID value in SampleMethod (FK) column that does not have related record in table SampleMethod. The following view will give you the missing MethodID's

SELECT dbo.FieldSamp.MethodID, dbo.FieldSamp.OtherStuff, dbo.SampleMethod.MethodID AS MethodIDinSampleMethod
FROM dbo.SampleMethod INNER JOIN
dbo.FieldSamp ON dbo.SampleMethod.MethodID = dbo.FieldSamp.MethodID
WHERE (dbo.SampleMethod.MethodID IS NULL)

You can then write an insert script to update the SampleMethod tbl
Go to the top of the page
post Jun 15 2018, 02:24 PM

Posts: 75
Joined: 21-July 15

Hi not sure if you can do a cascade update of the type your trying - happy to be proved wrong.
An alternative approach would be

(a) add a new record in your master table with an ID of 'flow' other details same as the original 'f' record
(b) do an update on the other table changing the foreign key field to 'flow' where it is currently 'f'
© delete the 'f' record from the master table

Hope this helps.
Go to the top of the page
post Jun 16 2018, 04:58 PM

Posts: 109
Joined: 21-February 03
From: Camarillo, CA


Your update can cascade if you have the FK defined as ENFORCED ... AND you set the update action to CASCADE.

Oops, I just re-read the original post again.
If you want the keys to CASCADE upward; from the Child to the parent; it obviously can be done, but I'd advise not to.

You will have to set the foreign-key relationship to "Not enforced".
Set the Delete/Update options to "NO ACTION" <-- not CASCADE
THEN, you'll have to write a trigger to do the processing for you.

In actuality, you no longer have a "real" and functional foreign-key.

My suggestion is to remove the foreign key.
Synchronize the child table data.
Re-Apply the ENFORCED foreign-key (with CASCADE).
Then ONLY change the parent component of the foreign-key and let it cascade.

Go to the top of the page
post Jun 17 2018, 08:25 PM

Posts: 300
Joined: 4-April 12
From: Bendigo, Australia

Thanks all,

I'm still not sure why I got the error but I just got rid of the relationship and dealt with each table separately. I was trying to make the change on the PK side and have it cascade to the related FieldSamp table. I guess I just need to do the translation when I transfer the data from the old database to my new production database.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    25th May 2019 - 11:23 PM