SQL Default Constraint

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

Create SQL Default Constraint

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

Set Default Constraint using SSMS

To create a SQL Server Default Constraint using Management Studio, please go to Object Explorer. Expand the Database folder in which the table had. Please select the table to create it (here it is Custrecords), and right-click on it will open the context menu. Please select the Design option.

Open Table in Design Mode 1

Once you click the Design option, the management studio will open the corresponding table in design mode. As you see, the table had seven columns of different data types, and our task was to add the SQL Default Constraint to the Profession column.

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

Add Value or Binding Information of a Column 3

Let me insert a value into the SQL Server table using the INSERT Statement.

INSERT INTO [dbo].[CustRecords]
           ([CustKey], [FirstName], [LastName], [BirthDate], [EmailAddress], [Yearly Income])
     VALUES
           (1, 'Imran', 'Khan', '10-08-1985', 'abc@abc.com', 15900, 'Skilled Professional')
GO
Messages
--------
(1 row(s) affected)

Let me show you the Data.

View the Table Data 5

Though it has the default values, it is inserting the data I want to insert. Now, Let me insert the values without the profession column.

INSERT INTO [dbo].[CustRecords]
           ([CustKey], [FirstName], [LastName], [BirthDate], [EmailAddress], [Yearly Income])
     VALUES
           (2, 'Steve', 'Williams', '10-04-1989', 'abcd@abcd.com', 15500),
    (3, 'Doe', 'Lara', '10-08-1985', 'xyz@abc.com', 15000 ),
    (4, 'Ramesh', 'Kumar', '10-08-1985', 'mnop@abc.com', 65000)
GO
SQL Default Constraint 6

It is inserting the default values into the profession column.

Select Statement to View 7

Create using query

Let us create a default constraint in the SQL server using the Transact statement. Here, we will set the default values at the time of table creation.

Please refer to Create Table article.

-- Example
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

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
Messages
--------
Command(s) completed successfully.

We added it with the standard value as Software Developer. It is the same as we create any other constraint. The SSMS is intelligent enough to set the default value for you.

Let me insert the values without the profession column

INSERT INTO [dbo].[CustRecords]
	  ([CustKey], [FirstName], [LastName], [BirthDate], [EmailAddress], [Yearly Income])
     VALUES
           (1, 'Tutorial', 'Gateway', '10-04-1995', 'ab@abd.com', 12500),
	   (2, 'Steve', 'Smith', '10-04-1989', 'abcd@abcd.com', 15500),
	   (3, 'Jack', 'Lara', '10-08-1985', 'xyz@abcd.com', 15000 ),
	   (4, 'Ramesh', 'Kumar', '10-05-1983', 'mnopq@abcs.com', 65000)
GO
Insert Values 9

Add SQL ServerDefault Constraint on Existing table

How to add Default Constraint to the existing table? For this, we create a new table.

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 to the above table. As you can see from the below, we are using the Alter Table Statement to alter the table content. Then used the ADD Constraint statement to add the default constraint.

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

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

Add SQL Default Constraint on Existing Table 10

Delete Default Constraint in SQL Server using SSMS

Please select the table -> Column on which you want to add, then go to the Column properties and remove the default value.

Delete SQL Default Constraint 11

Delete or Drop Default Constraint

If you know the name, use the DROP Constraint statement along with the ALTER TABLE Statement.

-- DROP SQL Default Constraint
ALTER TABLE [dbo].[CustRecords]   
DROP CONSTRAINT DF_CustRecords_Profession;  
GO

If you don’t know the name, use the first SELECT Statement to get all the Constraint names.

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