Resize or Modify a MS SQL Server Table Column with Default Constraint using T-SQL Commands
If you are sql programming on MS SQL databases or if you are a SQL Server admin dealing with Microsoft SQL Server administration, you might probably need to alter table column to resize or modify the size of an existing table column with a DEFAULT Constraint already defined on the related column.
As an IT professional working on database programming I had to alter table columns to keep generally more data, so resize and modify their size length without changing the table column data type.
I mean, as a developer I felt the requirement of altering a table column defined as nvarchar(100) to nvarchar(2000) for instance.
An other sample may be, you can alter table column with data type smallint to int.
Of course you can imagine that it is not possible to alter a table column with varchar() data type to integer or a bit column to varchar() column.
If a constraint is not defined on the sql database table column, you can easily change and resize such table column sizes.
But what happens if a default constraint is already created and declared on the sql table column?
ALTER TABLE Customers ALTER COLUMN Country nvarchar(5)
Msg 5074, Level 16, State 1, Line 1
The object 'DF_Customers_Country' is dependent on column 'Country'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Country failed because one or more objects access this column.
The above ALTER TABLE ... ALTER COLUMN ... t-sql command execution is resulted with the above mentioned error.
What is understood from the error message is, since a dependent object DF_Customers_Country (which is a table column default constraint) exists on the sql database, you can not run the ALTER TABLE ALTER COLUMN sql statement.
So the solution for such a SQL Server problem is going to be ;
dropping or disabling the DEFAULT Constraint on the table column,
then modifying the table column data type and/or data size,
and finally re-creating or enabling the default constraint back on the sql table column.
Below you will see how this strategy is implemented on a sample SQL Server 2005 database table with sample sql codes and data.
Here is the sample sql database table on which we create data columns with default constraint on it.
As you see from the below sample sql codes, the SQL Server admin has created sql database table Customers and defined the default constraint DF_Customers_Country on Country named table column.
CREATE TABLE Customers (
CustomerId int identity(1,1),
CustomerName varchar(100),
Country nvarchar(3),
TotalPoints smallint DEFAULT 0
)
GO
INSERT INTO Customers (CustomerName) VALUES ('Bill Gates')
GO
ALTER TABLE Customers ADD CONSTRAINT DF_Customers_Country DEFAULT 'US' FOR Country
GO
SELECT * FROM Customers
GO
And I decided to resize the Country titled table column from nvarchar(3) to nvarchar(5) using the below ALTER TABLE ... ALTER COLUMN sql syntax which failed because of the default constraint.
ALTER TABLE Customers ALTER COLUMN Country nvarchar(5)
The error message thrown from the sql engine is "The object 'DF_Customers_Country' is dependent on column 'Country'."
So the below t-sql codes will implement the sql solution suggestion we have discussed at the beginning of this article.
First step is to drop the default constraint from the sql database.
Since now there is no dependent object on the SQL database table column, alter table column data type, data size, or data length.
Last step is creating back the default constraint on the SQL Server table column.
IF EXISTS (
SELECT * FROM sys.objects WHERE NAME = N'DF_Customers_Country'
)
ALTER TABLE Customers DROP CONSTRAINT DF_Customers_Country
GO
ALTER TABLE Customers ALTER COLUMN Country nvarchar(5)
GO
ALTER TABLE Customers
ADD CONSTRAINT DF_Customers_Country
DEFAULT 'US' FOR Country
Now the below sample is for altering an smallint columnt into a integer data type column.
If you execute the below sample t-sql statements command by command, you can see how a column which has a default constraint can be resized or modified using t-sql.
ALTER TABLE Customers ALTER COLUMN TotalPoints int
/*
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Customers__Total__567ED357' is dependent on column 'TotalPoints'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN TotalPoints failed because one or more objects access this column.
*/
GO
IF EXISTS (
SELECT * FROM sys.objects WHERE NAME = N'DF__Customers__Total__567ED357'
)
ALTER TABLE Customers DROP CONSTRAINT DF__Customers__Total__567ED357
GO
ALTER TABLE Customers ALTER COLUMN TotalPoints int
GO
ALTER TABLE Customers
ADD CONSTRAINT DF__Customers__Total__567ED357
DEFAULT 0 FOR TotalPoints
How to add default value to existing column on a MS SQL Server database table using T-SQL Commands
Since the last step is creating back the default constraint on the sql table column, it will be better to remember the t-sql syntax for creating default constraints.
Otherwise if you execute t-sql code similar to shown below using SET DEFAULT you will get the incorrect syntax error.
ALTER TABLE Customers ALTER COLUMN CustomerName SET DEFAULT 'No Name'
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SET'.
The correct way or the correct t-sql syntax for creating default constraints on an existing table column is ALTER TABLE ... ADD CONSTRAINT ... DEFAULT ... FOR ... as shown below :
ALTER TABLE Customers
ADD CONSTRAINT DF_Customers_CustomerName
DEFAULT 'No Name' FOR CustomerName