Update Primary/Foreign Key Value
-
Let me also remind you that SQL 7 does not supports CASCADE Actions that's for 2000
* Noel -
Thanks Noeld. What is your recommendation for my situation?
-
Still can't be reminded when you don't know it in the first place...
-
If you can't upgrade to 2000 you pretty much have very limited options:
1. Remove fk/pkey - perform update accross all tables - put back fk/pkeys (this is the standard way)
2. Create a copy of the DB in 2000 - alter your keys with cascade - update the parents - remove the cascade - use DTS to copy "objects" back to SQL 7 (this is probably the fastest if you don't need to go back to 7)
3.bcp data out -- delete tables- DTS data in and in the transformation use the values to be replaced (very unconventional but no schema changes are needed)
Remember to back up before you do anything
Just my $0.02
* Noel -
And I thaught we only had to backup after
. -
Thanks Noeld. Great information. If I do migrate the database to SQL Server 2000, is there a set of procedures that I should follow before doing so? Any further information would be greatly appreciated.
-
I've been biten by that alooooooot of times ( Uh I don't have a backup...
) that's why I keep saying this to everyone I can
* Noel -
It has been a while since I upgraded an SQL7 to 2k but the process should be painless. There is a Backward Compatibility Chapter in BOL that describes the possible changes/issues. Those are very rare this days but you never know
* Noel -
Thanks all. I will be suggesting to the client to upgrade to SQL Server 2000 (especially since they already have a MSDN subscription including the newer DB) and then proceeding with the UPDATE CASCADE option.
Is there a better way to display/query in EM the primary/foreign key constraints for a DB other than using the 'script' option?
-
Cory,
EM does not supports such list. You are better of if you need some graphical feed back with the "Diagram" or a Modeling tool that can perform reverse engineering
Other than that the 'script' is your best friend
* Noel -
IIRC the last time I did this
Create a new record in the 'primary' table(s) with the same details except with the new patrol_no
Update patrol_no on all other tables that reference 'primary' table(s)
Delete unwanted record(s)
And do it all in a transaction to check all OK and retain integrity
Far away is close at hand in the images of elsewhere.
Anon. -
Don't really understand what you are suggesting David. Wouldn't that create the same problem as before? Cannot update values that are primary/foreign key values in multiple tables.
-
You can change a column used in a foreign-key as long as the primary key exists.
You can change/delete a primary key as long no foreign-keys point to it.
Therefore, e.g.
[Table1]
ID int PK
Col1...
Col2...
Col3...
[Table2]
ID int PK
ID1 int FK (to [Table1])
[Table3]
ID int PK
ID1 int FK (to [Table1])
BEGIN TRANSACTION
INSERT INTO [Table1] (ID,Col1,Col2,Col3)
SELECT [NewID],Col1,Col2,Col3 FROM [Table1]
WHERE ID=[OldID]
UPDATE [Table2] SET ID1=[NewID] WHERE ID1=[OldID]
UPDATE [Table3] SET ID1=[NewID] WHERE ID1=[OldID]
DELETE FROM [Table1] WHERE ID=[OldID]
COMMIT TRANSACTION
Obviously if the primary/foreign key combinations are more complicated then this code becomes longer.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply