June 25, 2008 at 1:35 pm
First off, I am not a sql guru (I know enough to really dangerous ) nor am I a DBA...
The delima I am facing is that I have a SQL backend with an access front. (I have it - did NOT build it )
The new reuirement is to modify a PK from an int to a varchar(30).
[The int was never set to seed so... the problem now is there are to many records and to many users and there are dups being created.]
I have attempted this about 6 to 8 times(lost track) and each time I think I have made progress, it seems I am farther behind as I broke everything that was ok prior.
So 148 tables numerous PK,FKs contraints etc....
Is there a way to script this all out of the good db?
Can it modified to change the int in all the related areas?
Can it drop all the contraints for the changes to happen?
HOW THE HECK IS THIS DONE!!!! AHHHHHHHHHHH!!!!!!!
I am running out of time and possibly this job 🙂 so if there is anyone out there who can point me in the right direction, I REALLY would appreciate it!
If you have gotten this far I would like to Thank you for your time!
June 25, 2008 at 1:59 pm
The way this works, is a new table is created, the data copied, the old one dropped, and the new one renamed.
It doesn't matter which GUI you use, this is how Microsoft has scripted it. You could reorder things, but this is what you need to do. If you have FKs, then it might be a little more involved since you might need to drop and recreate the FKs.
That takes time. The data copy, hopefully no errors, requires time to move the physical data and the drop requires exclusive locks. If you script things, this might go quicker, but you might also need to get people kicked out of the system.
What is breaking or what isn't working?
June 25, 2008 at 2:17 pm
WHat breaks is, I can not seem to KEEP the contraints after the change, it is like a ripple affect...
If I mod the main table, before the mod there are 40+ , after the mod there are only 23 :~
So I really dont know what is wrong because I do not get any errors...
I have tried via 2005 interface and 2000 ent mngr to no avail...
I guess I really am hacking and hoping as I have ne real CLEAR idea how this SHOULD be done correctly... 🙁
June 25, 2008 at 2:22 pm
The proper way to do this is create a script that drops all the FKs, modifies all the columns, then re-creates all the FKs.
In Query Analyzer, find all the FKs, and script them as Drop to a connection (editor window). Then script the create scripts at the end of that script.
Test that in a dev copy of the database, make sure you end up with the same constraints at the end that you had at the beginning.
Then, in the middle of that script, have your alter table commands.
Again, test that in a dev copy of the database.
It takes a few minutes to set up, but it should run quite rapidly and very well. I've done this before and it worked.
You should end up with something that looks like:
ALTER TABLE [dbo].[ListsArchive]
DROP CONSTRAINT [FK__ListsArch__DNMSt__71D1E811]
go
ALTER TABLE [dbo].[ListsActive]
DROP CONSTRAINT [DF__ListsActi__DNMSt__145C0A3F]
go
alter table dbo.ListsArchive
alter column DNMStatusID int
go
alter table dbo.ListsActive
alter column DNMStatusID int
go
ALTER TABLE [dbo].[ListsArchive]
WITH CHECK ADD FOREIGN KEY([DNMStatusID])
REFERENCES [dbo].[ListsDNMStatuses] ([StatusID])
go
ALTER TABLE [dbo].[ListsActive]
WITH CHECK ADD FOREIGN KEY([DNMStatusID])
REFERENCES [dbo].[ListsDNMStatuses] ([StatusID])
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 3:14 pm
Hello again,
Your process sounds nice and logical. THANKS!
I have an issue already however 🙁
I can not seem to find in the query anylizer wehere to:
"In Query Analyzer, find all the FKs, and script them as Drop to a connection (editor window). "
Do you have idiots instructions guide? 🙂
I managed to find them using a search window but there is no script out context menu or main menu.... :~
Thank you for your time again!
June 25, 2008 at 3:23 pm
I'm assuming you are using Visual Studio and don't have access to the standard SQL Server tools.
There is a table called sysreferences that is in all databases, it is this that keeps system data on foreign keys.
There are 3 columns in particular that are of use.
constid is the id of the foreign key
fkeyid and rkeyid are two I get mixed up but one is the id of the parent table in the relationship and the other of the child.
There are also columns with the column id of the fields participating in the relationship.
If you ran
SELECT OBJECT_NAME(constid) , OBJECT_NAME(rkeyid), OBJECT_NAME(fkeyid)
from sysreferences you would get basic information on all the foreign keys in your database.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply