Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
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
show 2 more comments

migrated from stackoverflow.com Dec 28 '12 at 23:09

2 Answers

up vote 3 down vote accepted
  1. Script out Drop and Create All 30 JF.
  2. Drop all 30 KF
  3. Alter Columns
  4. Create all 30 KF
share|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. – fortheworld Jan 3 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 at 15:03
up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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