SQL Default Constraint

SQL Default Constraint is used to assign default values to the SQL table columns. In general, every column accepts either Nulls, or a Value. If the user forgot to enter the value then SQL Server will assign NULL value to the column. In those cases, we can use the SQL Server Default constraint to replace those Nulls with default value. In this article we will show you, how to assign default values in SQL server with examples.

Create SQL Default Constraint

We can create a SQL Server Default Constraint using both the Transact SQL query, and the SQL Server Management Studio.

Set SQL Default Constraint using SSMS

In this example we will show you, How to create a Default Constraint in SQL server using the SQL server management Studio. To do so, Please go to the Object Explorer. Within the object explorer, Expand the Database folder in which the table had. Please select the table on which you want to create a Default Constraint (here it is Custrecords), and Right click on it will open the context menu. Please select Design option as shown below

Once you click the Design option, SQL management studio will open the corresponding table in design mode. From the below screenshot you can observe that, We had 7 columns of different data types, and our task is to add the Default Constraint to Profession column.

Please select the Column name, and provide the default values at the Default Value or Binding Column property. For this example, we are assigning the Software Developer as the default value for the profession column.

Let me insert value into the table using the INSERT Statement.

SQL CODE

-- Inserting Default Values in SQL Server
USE [SQL Tutorial]
GO

INSERT INTO [dbo].[CustRecords]
           ([CustKey], [FirstName], [LastName], [BirthDate], [EmailAddress], [Yearly Income])
     VALUES
           (1, 'Imran', 'Khan', '10-08-1985', '[email protected]', 15900, 'Skilled Professional')
GO

OUTPUT

Let me Show you the Data

As you can see from the above screenshot, though, it has the default values, SQL is inserting the data that I want to insert. Now, Let me insert the values without the profession column

SQL CODE

-- Inserting Default Values in SQL Server
USE [SQL Tutorial]
GO

INSERT INTO [dbo].[CustRecords]
           ([CustKey], [FirstName], [LastName], [BirthDate], [EmailAddress], [Yearly Income])
     VALUES
           (2, 'Steve', 'Williams', '10-04-1989', '[email protected]', 15500),
	   (3, 'Doe', 'Lara', '10-08-1985', '[email protected]', 15000 ),
	   (4, 'Ramesh', 'Kumar', '10-08-1985', '[email protected]', 65000)
GO

OUTPUT

You can see from the below screenshot, SQL Server is inserting the default values to the profession column.

Create SQL Default Constraint using T-SQL

In this example we will show you, How to create a default in SQL server using the Transact SQL statement. Here we will set the default values at the time of table creation. I Suggest you to refer Create Table article to understand the Create Table statement.

SQL CODE

USE [SQL Tutorial]
GO
CREATE TABLE [dbo].[CustRecords](
	[CustKey] [int] NOT NULL PRIMARY KEY,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NULL,
	[BirthDate] [date] NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[Yearly Income] [money] NULL,
	[Profession] [nvarchar](100) NOT NULL DEFAULT ('Software Developer'),
)
GO

OR

SQL CODE

USE [SQL Tutorial]
GO
CREATE TABLE [dbo].[CustRecords](
	[CustKey] [int] NOT NULL PRIMARY KEY,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NULL,
	[BirthDate] [date] NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[Yearly Income] [money] NULL,
	[Profession] [nvarchar](100) NULL CONSTRAINT [DF_CustRecords_Profession]  DEFAULT ('Software Developer'),
)
GO

As you can see from the above code snippet, we just added the default constraint with default value. it is same as we create any other constraint. SSMS is intelligent enough to set default value for you.

OUTPUT

Let me insert the values without the profession column

SQL CODE

-- Inserting Default Values in SQL Server
USE [SQL Tutorial]
GO

INSERT INTO [dbo].[CustRecords]
	  ([CustKey], [FirstName], [LastName], [BirthDate], [EmailAddress], [Yearly Income])
     VALUES
           (1, 'Tutorial', 'Gateway', '10-04-1995', '[email protected]', 12500),
	   (2, 'Steve', 'Smith', '10-04-1989', '[email protected]', 15500),
	   (3, 'Jack', 'Lara', '10-08-1985', '[email protected]', 15000 ),
	   (4, 'Ramesh', 'Kumar', '10-05-1983', '[email protected]', 65000)
GO

OUTPUT

SQL Default Constraint on Existing table

In this example we will show you, How to add SQL Server default constraint on existing table. For this, we are going to create a new table using the following statement

SQL CODE

USE [SQL Tutorial]
GO
CREATE TABLE [dbo].[CustRecords](
	[CustKey] [int] NOT NULL PRIMARY KEY,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NULL,
	[BirthDate] [date] NULL,
	[EmailAddress] [nvarchar](50) NULL,
	[Yearly Income] [money] NULL,
	[Profession] [nvarchar](100) NOT NULL
)
GO

Now let me add the default constraint. As you can see from the below screenshot we are using the Alter Table Statement to alter the table content, and then used the ADD Constraint statement to add the default constraint.

SQL CODE

USE [SQL Tutorial]
GO
ALTER TABLE [dbo].[CustRecords]   
ADD CONSTRAINT DF_CustRecords_Profession DEFAULT ('Software Developer') FOR Profession;  
GO

Let me show you the internal code generated by the SQL, by right clicking on the table -> script as – > create to new query window option

Delete SQL Default Constraint using SSMS

Within the object explorer, Expand the Database folder in which the table had. Please select the table -> Column on which your default constraint holds, then go to the Column properties, and remove the default value as we shown below.

Delete SQL Default Constraint using T-SQL

If you know the Default constraint name then use the DROP Constraint statement along with the ALTER TABLE Statement

SQL CODE

USE [SQL Tutorial]
GO

ALTER TABLE [dbo].[CustRecords]   
DROP CONSTRAINT DF_CustRecords_Profession;  
GO

If you don’t know the constraint name then use the first SELECT Statement to get the Constraint name.

SQL CODE

USE [SQL Tutorial]
GO
-- To find the Default Constraint Name
SELECT name  
FROM sys.default_constraints  
WHERE OBJECT_NAME(parent_object_id) = N'CustRecords';  
GO  
-- Delete or Drop the Constraint 
ALTER TABLE [CustmerRecords]  
DROP CONSTRAINT DF_CustRecords_Profession

OUTPUT

Thank You for Visiting Our Blog