Cascade update

Question

Cascade update

Abhijit More

SSCertifiable

Points: 6897
More actions
January 29, 2009 at 10:07 pm

#126001

I have faced one problem for updating parent-child tables, would appreciate your suggestions.
ParentTable
=========
ParentCode VARCHAR(10) PRIMARY KEY,
ParentData VARCHAR(MAX)
ChildTable
=========
ChildCode VARCHAR(10) PRIMARY KEY,
ParentCode VARCHAR(10),
ParentData VARCHAR(MAX)
I want to update particular ParentCode(key column) & also want to update the associated parentCode in child table
Query : Update ParentTable SET ParentCode = "001" WHERE ParentCode = "Scr001"
Query : Update ChildTable SET ParentCode = "001" WHERE ParentCode = "Scr001"
Q.1 = Is this a valid update?
Q.2 = How can we achieve this using cascade update?
Q.3 = The only option i can see here to disable the constraint, update data, enable constraint? Am i right? if yes, there is one problem in this, as I am running these queries on Production Env. this could be wrong way or can rely on this, where continuous inserts are happening?
Abhijit - http://abhijitmore.wordpress.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

Your Child table should be referenced by Primary-Foriegn key relationship.

If you are using CASCADE DELETE/UPDATE the consecutive members in the child table will get be updated/deleted according to the changes u make to the primary key in the parent table.

Reference your child table this way:

ALTER 'childtable'

ADD CONSTRAINT 'foriegn_keyname' FORIEGNKEY ('columnname'[/i])

REFERENCES 'Parenttable(primarykeyname)'

ON UPDATE CASCADE

follow this link for a better understanding:

http://www.mssqltips.com/tip.asp?tip=1296

Lowell SSC Guru Points: 323518 More actions · Answer 2

when you create a foreign key, you can add the extra commands ON UPDATE CASCADE ON DELETE CASCADE, and then changes to teh parent will automatically propigate to the foreign key:

the easiest thing to do would be to drop the FK, and re-add it with the cascade commands.

try this example.

[font="Courier New"]

CREATE TABLE ParentTable(

ParentCode VARCHAR(10) PRIMARY KEY,

ParentData VARCHAR(MAX) )

CREATE TABLE ChildTable(

ChildCode VARCHAR(10) PRIMARY KEY,

ParentCode VARCHAR(10) REFERENCES ParentTable(ParentCode) ON DELETE CASCADE ON UPDATE CASCADE,

ParentData VARCHAR(MAX) )

INSERT INTO ParentTable

SELECT 'scr001','somedata' UNION

SELECT 'scr002','moredata'

INSERT INTO ChildTable

SELECT 'CHD001','scr001','more stuff' UNION

SELECT 'CHD002','scr001','other stuff' UNION

SELECT 'CHD003','scr002','more stuff' UNION

SELECT 'CHD004','scr002','other stuff'

SELECT * FROM ParentTable

SELECT * FROM ChildTable

UPDATE ParentTable SET ParentCode='001' WHERE ParentCode='scr001'

SELECT * FROM ParentTable

SELECT * FROM ChildTable[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!