Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

SQL Default Constraint

by suresh

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 a NULL value to the column. In those cases, we can use the SQL Server Default constraint to replace those Nulls with the default value.

Create SQL Default Constraint

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

Set Default Constraint using SSMS

To create a SQL Server Default Constraint using Management Studio, Please go to the Object Explorer.

Expand the Database folder in which the table had. Please select the table to create a Default Constraint (here it is Custrecords), and right-click on it will open the context menu. Please select the Design option

SQL Default Constraint 1

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

SQL Default Constraint 2

Please select the SQL Server 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.

SQL Default Constraint 3

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

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

Let me show you the Data

SQL Default Constraint 5

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

-- Inserting Default Values in SQL Server
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
SQL Default Constraint 6

SQL Server is inserting the default values to the profession column.

SQL Default Constraint 7

Create SQL Default Constraint using query

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

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

We 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.

SQL Default Constraint 8

Let me insert the values without the profession column

-- Inserting Default Values in SQL Server
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
SQL Default Constraint 9

SQL Default Constraint on Existing table

How to add a default constraint on 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 the default constraint. 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 the SQL, by right-clicking on the table -> script as – > create to new query window option

SQL Default Constraint 10

Delete Default Constraint using SSMS

Please select the table -> Column on which your default constraint holds, then go to the Column properties, and remove the default value.

SQL Default Constraint 11

Delete Default Constraint using Drop Constraint

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

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

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

-- 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
SQL Default Constraint 12

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy