This is not an article or a script. I am posting this just to share my experience and I feel it can save a lot of time in a specific situation.
Problem
– We have a table let’s say Customer and this table has an alpha numeric primary
key CustomerID varchar(10). Due to unexpected growth in volume of data we
figure out varchar(10) was not sufficient and we decided to make it varchar(50).
When
we ran the below script, we were getting error due to foreign key constraint.CustomerID
was being referenced in many table maintaining the relations.
ALTER TABLE [dbo].[Garmentlayer]
ALTER COLUMN LAYERNAME varchar(50) ;
We
tried to dropping the constraints , alter the column and then recreate the
constraints but it was failing with the following error.
The object 'PKCustomerID' is dependent on
column ‘CustomerID’.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN CustomerID
failed because one or more objects access this column.
Again we tried with disabling all the constraints, alter
the column and enable the constraints. It failed again. After doing some
research we found that it is failing because it is a foreign key to many table
and foreign key should be unique and the solution was very simple. Use the Not
Null with alter column. Here is the script –
ALTER TABLE [dbo].[Garmentlayer]
ALTER COLUMN LAYERNAME varchar(50) NOT NULL;
Thanks
