Written by 13:43 Database development, Tables

SQL DEFAULT Constraint to Insert Column with a Default Value to SQL Server Table

CodingSight - Insert Column with a Default Value to an Existing Table

To insert data into a table having columns with default constraints, we can use the DEFAULT constraint to a default value in a table. This article will cover the following aspects:

  • The DEFAULT constraint and required permission to create them.
  • Adding a DEFAULT constraint while creating a new table.
  • Adding a DEFAULT constraint into an existing table.
  • Modifying and Viewing the constraint definition using T-SQL scripts and SSMS.

What is DEFAULT CONSTRAINT in SQL Server?

A default constraint specifies the default value for the column.

When we execute the INSERT statement but do not specify any value for the column with the default constraint created, SQL Server inserts the default value specified in the DEFAULT constraint definition.

To create a default constraint, you need the ALTER TABLE and CREATE TABLE permission.

Adding a DEFAULT Constraint While Creating a New Table

There is a table named SalesDetails. When we insert data into the table without specifying the Sale_Qty column value, the query must insert zero. To do that, I am creating a default constraint named DF_SalesDetails_SaleQty on the Sale_Qty column.

USE demodatabase 
go 
CREATE TABLE salesdetails 
  ( 
     id           INT IDENTITY (1, 1), 
     product_code VARCHAR(10), 
     sale_qty     INT CONSTRAINT df_salesdetails_saleqty DEFAULT 0 
  ) 

Now, let us test the constraint behavior by inserting some dummy records into it. Execute the following query:

INSERT INTO salesdetails 
            (product_code) 
VALUES     ('PROD0001')

Use the following query to view data from the table:

INSERT INTO salesdetails 
            (product_code) 
VALUES     ('PROD0001')

Output

Adding a DEFAULT constraint while creating a new table

As you can see, the zero has been inserted into the Sale_Qty column.

While creating a table, we do not specify the name of the DEFAULT constraint. SQL Server creates a constraint with a system-generated unique name.

Create the table using the below query:

USE demodatabase 
go 
CREATE TABLE salesdetails 
  ( 
     id           INT IDENTITY (1, 1), 
     product_code VARCHAR(10), 
     sale_qty     INT DEFAULT 0 
  )

Run the following script to view the constraint name:

SELECT NAME             [Constraint name], 
       parent_object_id [Table Name], 
       type_desc        [Object Type], 
       definition       [Constraint Definition] 
FROM   sys.default_constraints 

Output

The output of the script to view the constraint name

SQL Server has created a constraint with the system-generated name.

Adding a DEFAULT Constraint Into Existing Table

To add a constraint on an existing column of the table, we use the ALTER TABLE ADD CONSTRAINT statement:

ALTER TABLE [tbl_name] 
  ADD CONSTRAINT [constraint_name] DEFAULT [default_value] FOR [Column_name]

In the syntax,

  • tbl_name: specify the name of the table where you want to add the default constraint.
  • constraint_name: specify the desired constraint name.
  • column_name: specify the name of the column where you want to create the default constraint.
  • default_value: specify the value that you want to insert – an integer, character, or character string.

When we do not specify the value of the column_name in the INSERT statement, it inserts the value specified in the default_value parameter.

First, let us add the Product_name column into SalesDetails:

ALTER TABLE salesdetails 
  ADD product_name VARCHAR(500) 

We insert the data into the table without specifying the Product_name column value. The query must insert N/A.

To do that, I am creating a default constraint named DF_SalesDetails_ProductName on the Product_name column. The following query creates a default constraint:

ALTER TABLE dbo.salesdetails 
  ADD CONSTRAINT df_salesdetails_productname DEFAULT 'N/A' FOR product_name 

Now, let us observe the constraint behaviour. Insert a record without specifying the product name:

INSERT INTO salesdetails 
            (product_code, 
             product_name, 
             sale_qty) 
VALUES     ('PROD0002', 
            'Dell Optiplex 7080', 
            20) 

INSERT INTO salesdetails 
            (product_code, 
             sale_qty) 
VALUES     ('PROD0003', 
            50)

Once the record is inserted, run the SELECT query to view the data:

USE demodatabase 
go 
SELECT * 
FROM   salesdetails 
go

Output

Adding a DEFAULT constraint into an existing table

As you can see in the above image, the value of the Product_name column for PROD0003 is N/A.

Modifying the DEFAULT Constraint using T-SQL Scripts

We can change the definition of the default constraint: first, drop the existing constraint and then create the constraint with a different definition.

Suppose, instead of inserting N/A, we want to insert Not Applicable.  First, we must drop the DF_SalesDetails_ProductName constraint. Execute the following query:

ALTER TABLE dbo.salesdetails 
  DROP CONSTRAINT df_salesdetails_productname

When the constraint is dropped, run the query to create the constraint:

ALTER TABLE dbo.salesdetails 
  ADD CONSTRAINT df_salesdetails_productname DEFAULT 'Not Applicable' FOR 
  product_name 

Now, let us insert a record without specifying the product name:

INSERT INTO salesdetails 
            (product_code, 
             sale_qty) 
VALUES     ('PROD0004', 
            10)

Run the SELECT statement to view the data from the SalesDetails table:

USE demodatabase 
go 
SELECT * 
FROM   salesdetails 
go

Output

Modifying the DEFAULT constraint

As you can see, the value of the Product_name column is Not Applicable.

Viewing the DEFAULT Constraint Using SSMS

We can view the list of the DEFAULT constraint using SQL Server Management Studio and querying dynamic management views.

Open SSMS and expand Databases > DemoDatabase > SalesDetails > Constraint:

Viewing the DEFAULT constraint

You can see the two constraints named DF_SalesDetails_SaleQty and DF_SalesDetails_ProductName created.

Another way to view constraints by querying sys.default_constraints. The following query populates the list of default constraints and their definitions:

SELECT NAME                  [Constraint name], 
       Object_name(parent_object_id)[Table Name], 
       type_desc             [Consrtaint Type], 
       definition            [Constraint Definition] 
FROM   sys.default_constraints

Output

Another way to view constraints by querying sys.default_constraints. The output of the query to populate the list of default constraints and their definitions

We can use the sp_helpconstraint stored procedure to view the list of constraints created on the table:

EXEC Sp_helpconstraint 'SalesDetails'

Output

We can use the sp_helpconstraint stored procedure to view the list of constraints created on the table

The constraint_keys column shows the default constraint definition.

Dropping the Constraint

We can drop a constraint in several ways. We can do it by using any of the following methods:

  • ALTER TABLE DROP CONSTRAINT statement.
  • DROP DEFAULT statement.

Drop a Constraint Using ALTER TABLE DROP CONSTRAINT Command

To drop the constraint, we can use the ALTER TABLE DROP CONSTRAINT command. The syntax is following:

Alter table [tbl_name] drop constraint [constraint_name]

In the syntax,

  • tbl_name: specify the table name that has a column with default constraint.
  • constraint_name: specify the constraint name that you want to drop.

Suppose we want to drop the DF_SalesDetails_SaleQty constraint from the SalesDetails table. Run the query:

ALTER TABLE dbo.salesdetails 
  DROP CONSTRAINT [DF_SalesDetails_SaleQty] 

Execute the below query to verify that the constraint has been dropped:

SELECT NAME                  [Constraint name], 
       Object_name(parent_object_id)[Table Name], 
       type_desc             [Consrtaint Type], 
       definition            [Constraint Definition] 
FROM   sys.default_constraints

Output

Dropping the constraint

The constraint has been dropped.

Remove the DEFAULT Constraint Using the DROP DEFAULT Statement

Now, let us understand how we can remove the default constraint using the DROP DEFAULT statement. The syntax of the DROP DEFAULT statement is as follows:

DROP DEFAULT [constraint_name]
  • constraint_name: specify the constraint name that you want to drop.

To drop the constraint using the DROP DEFAULT statement, run the following query:

IF EXISTS (SELECT NAME 
           FROM   sys.objects 
           WHERE  NAME = 'DF_SalesDetails_ProductName' 
                  AND type = 'D') 
DROP DEFAULT [DF_SalesDetails_ProductName]; 

Summary

Thus, we have examined several essential aspects of work with the default constraints. We’ve learned how to create a default constraint while creating a new table or add a default constraint on an existing column of the table.

Also, we’ve explored the cases of modifying the default constraint definition, viewing the details of default constraints using different means, and dropping the default constraint.

We hope that this information and the practical examples we’ve examined will be helpful in your work.

Read Also

SQL INSERT INTO SELECT: 5 Easy Ways to Handle Duplicates

Tags: , Last modified: October 06, 2021
Close