How to change the value of a field used as a prmary key to join to tab

[CarrahaG]

Hello

We have a MS SQL Server database in which we need to change the value of a field. Unfortunately the columns in which that field exist are used to create a relationship between the two tables.

Table -> IM_SER_ACTIV

ITEM_NO SER_NO ACTIV_TYP DAT
------- ------------ --------- ---------
Q07105 S000B977136C R 8/27/2009
Q07105 S000B977136C C 1/18/2010



Table -> IM_SER

ITEM_NO SER_NO LOC_ID SER_COST
------- ------------ ------ --------
Q07105 S000B977136C ST02 136.8544


Is there a way we can change the value of SER_NO from S000B977136C to S000B977139C even though there is a relationship?

 

[bborissov]

It depends how you created that relationship.
If you created it with ON UPDATE CASCADE the you just have to change that value in your Parent table (SQL Server will handle the other changes).

Otherwise you should handle it by yourself.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[CarrahaG]

Hello Borislav

I did not create the database so I am not sure how the relationship was created. However, it does not seem to be created with ON UPDATE CASCADE

When I run the following command:

update IM_SER_ACTIV
set SER_NO = '000B986148C'
where (ITEM_NO = 'Q07105') and (SER_NO = 'S000B977136C')


I get the following error:

Error in statement # 1

Foreign key violation
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_IM_SER_ACTIV_IM_SER". The conflict occurred in database "ADSTEST", table "dbo.IM_SER"
[update IM_SER_ACTIV
set SER_NO = '000B986148C'
where (ITEM_NO = 'Q07105') and (SER_NO = 'S000B977136C')]

 

[bborissov]

Check:
ALTER TABLE ..... NOCHECK CONSTRAINT....
in BOL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Crowley16]

what the actual error is saying is that you are missing a entry in the parent table.

Even though it's possible to use nocheck to change this entry, it may make more sense to create the serial number entry in the parent table first, then update the child table.

Foreign keys are specially designed to stop you from inserting/creating records in a child table which doesn't have a corresponding entry in the parent table...

--------------------
Procrastinate Now!

 

[CarrahaG]

Hello Crowley16

So are you saying that I need to deleted the related records in each table and then add them again with the new field value? Is there not away I can just update that key field in both tables at the same time? The reason is that I would have to copy all other field values in that record to insert them in the new record.

 

[danvlas]

From BOL:
"To modify a FOREIGN KEY constraint, you must first delete the existing FOREIGN KEY constraint and then re-create it with the new definition."

So... delete the relationship between the 2 tables, then add it back, specifying the cascade update option:


ALTER TABLE IM_SER
DROP CONSTRAINT FK_IM_SER_ACTIV_IM_SER


ALTER TABLE IM_SER
ADD CONSTRAINT FK_IM_SER_ACTIV_IM_SER FOREIGN KEY (SER_NO)
REFERENCES YourPrimaryTablename (TheReferencedFieldName) On Update Cascade


After that, change the value in the primary table and the change will be also made in IM_SER.


Daniel Vlas
Systems Consultant