How to set a default value for an existing column in Sql Server
Updating a default value for an existing column is not as simple as execute a query, you have to consider a few things before doing it. We are going to see how to do it in the following article.
1. Demo Data
For this article we are going to create the following table
-- Create a new table called '[TESTTABLE]' in schema '[dbo]' -- Drop the table if it already exists IF OBJECT_ID('[dbo].[TEST_TABLE]', 'U') IS NOT NULL DROP TABLE [dbo].[TEST_TABLE] GO -- Create the table in the specified schema CREATE TABLE [dbo].[TEST_TABLE] ( [ID] INT NOT NULL PRIMARY KEY -- Primary Key column ,[TEST_DEFAULT_VARCHAR] NVARCHAR(50) NULL ,[TEST_DEFAULT_INT] INT NULL ,[TEST_DEFAULT_DATE] DATE NULL -- Specify more columns here ); GO INSERT INTO TEST_TABLE(ID ,TEST_DEFAULT_VARCHAR ,TEST_DEFAULT_INT ,TEST_DEFAULT_DATE ) VALUES (1,'TEST1',101,'20190101') ,(2,NULL,101,NULL) ,(3,NULL,NULL,'20200101');
The following table is going to be created.
2.- Updating target column with default values
Updating default value for an existing column in sql server consist in the following Steps:
Step 1: Changing default value to columns.
The first thing you need to do is to add a default value to the following columns.
ALTER TABLE TEST_TABLE ADD CONSTRAINT DV_TDEFVAR DEFAULT N'N/A' FOR TEST_DEFAULT_VARCHAR; ALTER TABLE TEST_TABLE ADD CONSTRAINT DV_TDEFINT DEFAULT 1 FOR TEST_DEFAULT_INT; ALTER TABLE TEST_TABLE ADD CONSTRAINT DV_TDEFDATE DEFAULT GETDATE() FOR TEST_DEFAULT_DATE;
When inserting data to the table you will notice that change was not applied as expected
INSERT INTO TEST_TABLE(ID) VALUES (4);
As you can see the inserted values that were not stated in the insert statement were inserted as NULL values, also there is no change in previous inserted NULL values into the table.
The reason for this is because our columns are allowing Null values, We need to make a change on them to disallow NULL values.
Step 2: Changing column from Null to not Null.
For disabling NULL values in our column, first we need to update existing NULL values that exist in our columns.
UPDATE TEST_TABLE SET TEST_DEFAULT_VARCHAR = N'N/A' WHERE TEST_DEFAULT_VARCHAR IS NULL; UPDATE TEST_TABLE SET TEST_DEFAULT_INT = 1 WHERE TEST_DEFAULT_INT IS NULL; UPDATE TEST_TABLE SET TEST_DEFAULT_DATE = GETDATE() WHERE TEST_DEFAULT_DATE IS NULL;
After doing that we can proceed to change column for not allowing NULL values.
ALTER TABLE TEST_TABLE ALTER COLUMN TEST_DEFAULT_VARCHAR NVARCHAR(50) NOT NULL; ALTER TABLE TEST_TABLE ALTER COLUMN TEST_DEFAULT_INT INT NOT NULL; ALTER TABLE TEST_TABLE ALTER COLUMN TEST_DEFAULT_DATE DATE NOT NULL;
Then you can try to insert into table NULL values, you will notice that this time default values are applied instead of NULL.
INSERT INTO TEST_TABLE(ID) VALUES (5);
As you can see when inserting the last row, default values were applied successfully in Sql Server.
Also if you want you can declare DEFAULT values when inserting
Applying a default value to an existing column in sql server can be complicated, but by following the previous steps and by understanding what’s going on between the relationship between NOT NULL and DEFAULT values this can be simplified.