Update PK on Foreign Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update PK on Foreign Key

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-06-15 : 12:47:11

I need to update a PK column that is referenced by a Foreign key.

I can't use CASCADE on the FK definition (loads of FKs, circular reference warning etc.)

I was assuming I could do


BEGIN TRANSACTION
UPDATE MasterTable 
SET    MyPKCol = 'NEW'
WHERE  MyPKCol = 'OLD'

UPDATE ChildTable 
SET    MyPKCol = 'NEW'
WHERE  MyPKCol = 'OLD'
-- Check for errors & rollback if necessary
COMMIT

but I haven't got that cracked, am I on the right track or do I need to do something more clever?

Thanks

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-15 : 13:00:58

[code]
USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO
CREATE TABLE myTable00(Col1 int, Col2 int, Col3 char(1)
, PRIMARY KEY(Col1,Col2),FOREIGN KEY(Col1) REFERENCES myTable99(Col1))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C'
GO

INSERT INTO myTable00(Col1,Col2,Col3)
SELECT 1,1,'A' UNION ALL
SELECT 1,2,'B' UNION ALL
SELECT 1,3,'C' UNION ALL
SELECT 2,1,'A' UNION ALL
SELECT 2,2,'B' UNION ALL
SELECT 2,3,'C'
GO

SELECT * FROM myTable99 l JOIN myTable00 r ON l.Col1 = r.Col1
GO

-- Would fail
-- UPDATE myTable99 SET Col1 = 4 WHERE Col1 = 1

-- Need to do
INSERT INTO myTable99(Col1,Col2)
SELECT 4, 'A'

INSERT INTO myTable00(Col1,Col2,Col3)
SELECT 4, Col2, Col3 FROM myTable00 WHERE Col1 = 1

DELETE FROM myTable00 WHERE Col1 = 1
DELETE FROM myTable99 WHERE Col1 = 1
GO

SELECT * FROM myTable99 l JOIN myTable00 r ON l.Col1 = r.Col1
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

[/code]

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-06-15 : 17:48:05

I was afraid that might be the case - so I've got to INSERT a dummy record in the "new" PK position, then UPDATE all the child records to point ot it, and then DELETE the old record having saved its column values so I can then UPDATE the new, dummy, record (there are a number of UNIQUE INDEXes on the table). What a load of hassle! pity that I can't just get everything "right" before doing a COMMIT!

Thanks for pointing me on the right path

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources