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
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
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
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
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:
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
We can use the sp_helpconstraint stored procedure to view the list of constraints created on the table:
EXEC Sp_helpconstraint 'SalesDetails'Output
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
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: sql insert, sql server Last modified: October 06, 2021
