Update Primary/Foreign Key Value
-
Is there a way to update a primary/foreign key value using the T-SQL Update statement?
-
Hi Cory,
I'm not sure I understand your question. Can you provide a few more details?
Ideally, we would like to see an example schema, sample data, and expected results.
The simple answer to your question is "yes". The details depend on exactly what you are asking.
Have a good day,
Wayne
-
Sure Wayne.
I have several tables in a database where a column, patrol_no is the only primary/foreign key in each of the tables. Recently, a request came in to update the value for patrol_no in all tables, however, when I tried to run the following update statement:
update asis_directed set patrol_no = 'BKSF05-043' where patrol_no = 'BKSF05-043A'
an error message was returned as the following:
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with TABLE REFERENCE constraint 'fk_asis_concurrent_asis_directed'. The conflict occurred in database 'asis', table 'asis_concurrent'.
The statement has been terminated.
No matter which table I try to update first, I still keep getting the same results as above (of course, with reference to other tables).
I was always under the impression that you could not use an UPDATE statement to change the value of a primary/foreign key value, but rather only use it to update datasets ( for example, in a WHERE caluse). If I am incorrect, then please let me now.
If you require further information, then I will be glad to provide.
Any suggestions or comments would be greatly appreciated.
-
Hi!!!
As far as PK/FK relationship concern .... there is not possiblilities of UPDATEing
statement..But it will possible for u when u remove the Relationship between them and UPDATE PK table and then UPADATE FK table...And again put Relationship between them.....just carefull while doing that ...remember also put other relationship exists with other tables....
Regards,
Papillon
-
You can also alter the relation and make it ON UPDATE CASCADE which wil propagate the changes to the child table(s).
-
Are there high risks to dropping the relationships on the related tables? I am always hesitant to do that, because of problem in the past, especially with older systems. This database is a SQL Server 7 database with no service packs applied (it is for one of my clients) and I have suggested to them an upgrade many times or at least apply the latest service pack. But with no success.
-
Are you reffering to my suggestion??
-
Yes, if you are suggesting to drop the relationship(s) and then perform the cascade update.
-
Not quite, I'm suggesting that you alter the relationships so that when you update the parent key that all the child keys get updated without you having to do anything.
The code that EM uses to do this operation goes like this, and I don't know if you can do it with an alter command but I doubt it.
BEGIN TRANSACTION
ALTER TABLE dbo.Comments
DROP CONSTRAINT FK_Comments_FkObjSQL
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Comments WITH NOCHECK ADD CONSTRAINT
FK_Comments_FkObjSQL FOREIGN KEY
(
FkObjSQL
) REFERENCES dbo.ObjSQL
(
PkObjSQL
) ON UPDATE CASCADE
GO
COMMIT
-
And don't forget:
"A table that has an INSTEAD OF trigger cannot also have a REFERENCES clause that specifies a cascading action. "
--BOL
jg
-
Hard to forget when you don't know about it
. Thanx for the info. -
How can I confirm that the key for each table is a primary/foreign key value?
-
Check the list of foreign keys on the main table?!
-
Is there code or an extended stored procedure for doing this?
-
Check the script section... there's surely something there.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply