3

I have a situation where a number of foreign keys are referencing a column in Table "A". I would like to change the length of the column in table "A". What is the best way to accomplish this in SQL Server 2008 R2? I am really hoping I don't have to drop all 30 foreign key constraints and re-create them again...

  • Why do you need ilastic field lengths? There's a max length anyway allowed for a field. At the point of designing, would have set to max... – bonCodigo Dec 28 '12 at 19:59
  • bonCodigo -- hindsight is 20/20... But this is a situation I am facing now. – Denis Dec 28 '12 at 20:01
  • and (b) primary key fields can't be varchar(max) – Denis Dec 28 '12 at 20:01
  • There you go the answer ;) Summarize my comments. – bonCodigo Dec 28 '12 at 20:08
  • @bonCodigo - See varchar(max) everywhere? for some reasons why that is not a good idea. – Martin Smith Dec 30 '12 at 17:43
4
  1. Script out Drop and Create All 30 JF.
  2. Drop all 30 KF
  3. Alter Columns
  4. Create all 30 KF
| improve this answer | |
  • can't I disable all the foreign keys somehow? – Denis Dec 28 '12 at 20:04
  • there is no disable option in MSSQL – Hiten004 Dec 28 '12 at 20:07
  • ugh... was hoping I didn't have to do it... I have to teach my users to abbreviate :-) – Denis Dec 28 '12 at 20:23
  • There is a way you can disable foreign keys on tables: ALTER TABLE TableName NOCHECK CONSTRAINT ALL. This however won't help when altering primary key/unique constraint columns, but it's useful information. – user2235 Jan 3 '13 at 2:25
  • @fortheworld - tried that. That only disable the constraints for operations like INSERT/UPDATE/DELETE. Doesn't disable the constraints when you are changing field lengths. – Denis Jan 7 '13 at 15:03
2

You can change column length in TableA via GUI (Management Studio), and all the foreign keys will be changed automatically.

| improve this answer | |
  • Good for small things that aren't critical but I just had this delete one of my main tables and not restore it when it timed out creating foreign keys through the diagram editor. – DShook Jun 4 '14 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.