When we create our tables, we set some columns to be null, so that some columns are never null. If the columns that we create as nullable are sometimes null, then we may want to set the default value to this column if the value is not given to this column. Let’s continue with an example to make it more descriptive.
Let’s create a table as follows. In this table, the names of the city and the famous food of these cities will be stored.
The CityName column is defined as NOT NULL as you see below. So this column will never be null. The FamousFood column is defined as NULL. So this column could also be null. Because there might be cities without famous food.
|
1 2 3 4 5 6 |
CREATE TABLE dbo.DefaultConstraintExample ( ID int NULL, CityName varchar(250) NOT NULL, FamousFood varchar(250) NULL ) ON [PRIMARY] |
Let’s do a few insert operations to our table as follows and then we’ll select values from the table.
|
1 2 3 4 |
INSERT INTO [dbo].[DefaultConstraintExample]([ID],[CityName],[FamousFood])VALUES(1,'Gaziantep','Lahmacun') INSERT INTO [dbo].[DefaultConstraintExample]([ID],[CityName],[FamousFood])VALUES(2,'Ankara',null) GO SELECT * FROM [dbo].[DefaultConstraintExample] |
As you can see, we set the FamousFood column for Ankara as null.
Let’s define a default constraint for FamousFood column with the help of the following script. This default constraint will set the value of the column to “There is not famous food” by default if the FamousFood column is null.
|
1 2 |
ALTER TABLE dbo.DefaultConstraintExample ADD CONSTRAINT DF_DefaultConstraintExample_FamousFood DEFAULT 'There is not famous food' FOR FamousFood |
After defining the default Constaint, add a few records as follows and see the result.
As you can see, we did not insert a famous food record for Istanbul. For this reason, by default, “There is not Famous Food” was set to FamousFood column.
|
1 2 3 |
INSERT INTO [dbo].[DefaultConstraintExample]([ID],[CityName])VALUES(3,'İstanbul') GO SELECT * FROM [dbo].[DefaultConstraintExample] |
