| Author |
Topic |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2004-11-28 : 18:07:33
|
| Hi FriendsI have table which has primary key of datatype varchar(5).we initially thought it may contain some chars so we defined it as varchar.but so far it has only numbers to it and we decided that we change it to int.This table is being referenced by 5 tables.could you tell me whats best way of handling this issue.My initial idea is to create a new field(of int) and re-create foreign key relation ship with child table pointing to new pkey.ofcourse i'll have to change all views ,sps which r using varchar pkey.am wondering u guys have better ideas.Thank u very much.Cheers |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-28 : 18:41:46
|
| How much data are you talking about?Are the other tables referencing the PK? If so you will have to change their datatypes tooI would drop a the foreign keysrename the tablecreate a new table with the correct structurecopy the data to the new tabledrop the old tableif necessary do the same for all the referencing tablesadd all the foreign keys again.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-11-28 : 18:45:55
|
| NrYes,there are 5 child tables refering this table and data is in thousands only.BTW you idea sounds good to me.What do u sggest for stored procs using this field ??Thank u veru much.Cheers |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-28 : 18:56:44
|
| If the stored procs don't depend on the datatype then there's no need to change them. If they do (e.g. they use variables to hold the value) then you should change them.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-11-28 : 18:59:50
|
| great Thank u for ur help NRCheers |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-11-28 : 21:59:30
|
| Hi NRI was told thatseems in EM ,If you make the changes to the parent table, it will make prompt you that the related tables require changes and will then make them for you - all based on the foreign key relationships. is this correct ?ThanksCheers |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-11-28 : 22:11:28
|
| Yes,It does.I just tried and it worked beautifully :-)Cheers |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-28 : 22:18:25
|
| Yep - but better than that you can get it to produce the script which you can look at (maybe amend) and save before running.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-11-28 : 22:19:57
|
| Nr,how can i get it to produce the script ?ThanksCheers |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-28 : 22:26:54
|
| Right click table in EM and choose DESIGNMake changes WITHOUT using SaveClick "Save Changes Script" ICON (3rd from right)There is an option to "Automatically generate change script on every save" which will ensure that you see the script, even if you accidentally press SAVE.FWIW I never let EM make the changes, I always creat the script, add that to my "rollout" scripts, and run it from my script.Kristen |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-11-28 : 22:48:59
|
| I just tried and it's great.now i've more control on what i want to do.Thank u very much Kristen and NRCheers |
|
|
|
|
changing datatype of primary key