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...

7
  • 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
    Commented Dec 28, 2012 at 19:59
  • bonCodigo -- hindsight is 20/20... But this is a situation I am facing now.
    – Denis
    Commented Dec 28, 2012 at 20:01
  • and (b) primary key fields can't be varchar(max)
    – Denis
    Commented Dec 28, 2012 at 20:01
  • There you go the answer ;) Summarize my comments.
    – bonCodigo
    Commented Dec 28, 2012 at 20:08
  • @bonCodigo - See varchar(max) everywhere? for some reasons why that is not a good idea. Commented Dec 30, 2012 at 17:43

2 Answers 2

4
  1. Script out Drop and Create All 30 JF.
  2. Drop all 30 KF
  3. Alter Columns
  4. Create all 30 KF
5
  • can't I disable all the foreign keys somehow?
    – Denis
    Commented Dec 28, 2012 at 20:04
  • there is no disable option in MSSQL
    – Hiten004
    Commented Dec 28, 2012 at 20:07
  • ugh... was hoping I didn't have to do it... I have to teach my users to abbreviate :-)
    – Denis
    Commented Dec 28, 2012 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
    Commented Jan 3, 2013 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
    Commented Jan 7, 2013 at 15:03
2

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

1
  • 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
    Commented Jun 4, 2014 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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