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.
| Author |
Topic |
|
veloopity
Starting Member
3 Posts |
 Posted - 2011-11-18 : 09:26:31
|
| A customer has a database (not designed by me) that has this problem:The companies table has a varchar(10) field for company names. This field is now too small (not surprisingly), a company with a name longer than 10 has to be added. Unfortunately, the original db designer has made this name field the primary key for the table. Apparently I can make it larger anyway, but then the database wants to also modify all related company name foreign keys in the other tables. I tried to do this (in a non-live older copy of this database) but there was an error because the transaction log was full. <sigh> The database is very large and contains hundreds of thousands of entries all of which have to be changed by this operation I guess.Is there some advice you can give me? Is there a way to close the database for other users, then temporarily disable logging, then make the primary key field larger, save this and wait for all subsequent changes, and then enable logging again and open the database for its normal users? I'm not much of a db admin, I'm a programmer that knows how to use SQL but when there is a database problem like this I'm not sure what to do.-Michael |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-18 : 09:47:05
|
| Run away?You can remove PKs, update all the tables individually then add them again.Might be a good time to add an integer PK to the companies table and make the company name an attribute.As an interim measure you could add the company name attribute and call the current column a code. Would mean that until client interfaces are updated they will get the code instead of the name - which would only make a difference for this new company.How many companies have a name less than 11 characters anyway - are you sure this is the full name and not an abbreviation?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
veloopity
Starting Member
3 Posts |
Posted - 2011-11-18 : 10:08:38
|
thx Nigel. Apparently the column was used for abbreviations in the beginning but now they want the full names. (I'm the new programmer for them as the old isn't available any more - I have completely redesigned database and frontend but the old version will still live for a while.) quote:
As an interim measure you could add the company name attribute and call the current column a code. Would mean that until client interfaces are updated they will get the code instead of the name - which would only make a difference for this new company.
I'm sorry, I have no idea what you're talking about here - can you rephrase "call the current column a code"? best -Michael |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-18 : 10:22:42
|
| Create a new column for the company name which will be a copy of the PK for all companies in the database currently.For the new company put the name in this new column and an abbreviation in the PK.Then everything will still work but anything that needs this name will have to be changed to get the company name from the new column.It's safer than expanding the column as you don't know what has restrictios on the size of the value.Of course fior the next decade (or few days until you get fed up) you'll be finding things that aer using the wrong column.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
veloopity
Starting Member
3 Posts |
Posted - 2011-11-18 : 10:35:26
|
| ah ok. Well this won't work without fiddling with the old code and I don't want to do that :) will try to drop the primary key constraints and rebuild them afterwards.-Michael |
|
|
|
|
|
|
Primary Key too small