The DEFAULT keyword can be used to assign a default value to a column. If the user’s INSERT statement does not have a specific value, the default value will be inserted into the column.
If we do not assign a default value to the column and the user leaves the column blank, NULL will be inserted into the column incase “allow null values” option is enabled. If the column value is mandatory and the “allow null values” option is not enabled, and the user does not provide a value for it, an error will be thrown.
A default value be a literal value, an expression, or a SQL function, such as GETDATE, can be used as the default value. Once we assign a column with a default value, a constraint will be created which enforces the column values to be unique.
What if we set a default value and then need to change it later?
Although there is no way to “update” the default value, dropping and re-creating the constraint with the new value will solve the problem. Consider the following example.
USE TestDB1;
GO
CREATE TABLE [SampleData] (
[IDCol] INT IDENTITY(201801,1),
[StringCol] VARCHAR(100) DEFAULT ‘BigDatanSQL’,
[DateCol] DATETIME DEFAULT GETDATE());
GO
INSERT INTO [SampleData] DEFAULT VALUES;
GO 12
Check the extended properties of the table “SampleData”. There will be two constraints associated with the table one with “StringCol” column and the other is on “DateCol” column.
Drop the constraint and recreate it with the updated value as shown below.
ALTER TABLE SampleData DROP CONSTRAINT DF__SampleDat__Strin__770B9E7A
ALTER TABLE SampleData
ADD CONSTRAINT DF__SampleData_StringCol DEFAULT ‘SQLBankBlogspot’ FOR [StringCol];
–Now insert the default values to see what is being inserted.
INSERT INTO [SampleData] DEFAULT VALUES;
GO 11
SELECT * FROM SampleData
Hope you find this article helpful.
Please subscribe to receive notifications on latest updates.
Big Data and SQL 
One comment