MS SQL Server Default Constraint

Hello friends, welcome to shrash studio learning, in this article we will clearly explain the DEFAULT constraint in SQL Server. This is a small but very useful feature — and it comes up in almost every beginner SQL Server interview. In this short guide we will use plain English and one real-world example to cover what a default constraint is, how to add one, how to drop one, how it behaves when you pass NULL explicitly, and the clean naming convention real projects follow.

What Is a DEFAULT Constraint?

A DEFAULT constraint is a rule you attach to a column so SQL Server can auto-fill a value whenever an INSERT statement does not provide one for that column. Instead of leaving the value as NULL or throwing an error, SQL Server quietly plugs in your chosen default.

Typical examples — a Status column that should default to 'active', a CreatedAt column that should default to the current date, or a LoyaltyPoints column that should default to 0. Defaults save you from writing the same value on every single insert.

A Small Example Table

Let's use a simple Users table throughout the article.

CREATE TABLE Users (
    UserId    INT IDENTITY(1, 1) PRIMARY KEY,
    Username  VARCHAR(50) NOT NULL,
    Email     VARCHAR(100) NOT NULL,
    Role      VARCHAR(20),
    IsActive  BIT,
    CreatedAt DATETIME
);

Without defaults, we'd have to remember to write 'user', 1, and GETDATE() on every single INSERT. Let's fix that.

Adding Defaults When Creating the Table

The cleanest way is to attach defaults directly in the CREATE TABLE statement. Use the CONSTRAINT keyword with a meaningful name so you can easily drop it later.

CREATE TABLE Users (
    UserId    INT IDENTITY(1, 1) PRIMARY KEY,
    Username  VARCHAR(50)  NOT NULL,
    Email     VARCHAR(100) NOT NULL,
    Role      VARCHAR(20)  CONSTRAINT DF_Users_Role      DEFAULT 'user',
    IsActive  BIT          CONSTRAINT DF_Users_IsActive  DEFAULT 1,
    CreatedAt DATETIME     CONSTRAINT DF_Users_CreatedAt DEFAULT GETDATE()
);

Now a very short INSERT works:

INSERT INTO Users (Username, Email) VALUES ('anita', 'anita@example.com');

SELECT * FROM Users;

Result — the three defaulted columns got filled in automatically:

UserId	Username	Email	Role	IsActive	CreatedAt
1	anita	anita@example.com	user	1	2026-05-02 10:30:00

Adding a DEFAULT to an Existing Table

If your table is already created without a default, use ALTER TABLE.

ALTER TABLE Users
ADD CONSTRAINT DF_Users_Role DEFAULT 'user' FOR Role;

Read it as a sentence: "For the Users table, add a constraint named DF_Users_Role that sets a default of 'user' for the Role column."

Adding a New Column With a Default

You can also add a new column and its default in one go. SQL Server will apply the default to every existing row automatically.

ALTER TABLE Users
ADD LoyaltyPoints INT
    CONSTRAINT DF_Users_LoyaltyPoints DEFAULT 0 WITH VALUES;

The WITH VALUES clause tells SQL Server to go back and set 0 for all existing rows — not just new ones. Without WITH VALUES, old rows stay NULL.

The Most Important Rule: NULL Is Not the Same as Missing

Key rule: A DEFAULT only kicks in when you don't mention the column in the INSERT. If you explicitly pass NULL, SQL Server respects your NULL and ignores the default.

-- Default WILL apply (column not mentioned)
INSERT INTO Users (Username, Email) VALUES ('rohit', 'rohit@example.com');
-- → Role = 'user'

-- Default WILL NOT apply (NULL explicitly passed)
INSERT INTO Users (Username, Email, Role) VALUES ('karan', 'karan@example.com', NULL);
-- → Role = NULL

If you absolutely want the default to always run when the app sends nothing meaningful, combine the default with NOT NULL on the column. Then any explicit NULL will be rejected.

Forcing the Default Using the DEFAULT Keyword

You can explicitly ask for the default in an INSERT or UPDATE using the DEFAULT keyword.

INSERT INTO Users (Username, Email, Role)
VALUES ('meera', 'meera@example.com', DEFAULT);

UPDATE Users SET Role = DEFAULT WHERE UserId = 2;

This is handy when you want to "reset" a row back to whatever the current default is.

Dropping a DEFAULT Constraint

Because you gave your constraint a meaningful name, dropping it is a one-liner.

ALTER TABLE Users DROP CONSTRAINT DF_Users_Role;

Why name your constraints? If you don't, SQL Server invents a random name like DF__Users__Role__3A81B327. Good luck finding that later. A clear name like DF_Users_Role makes maintenance painless.

Useful Built-In Default Values

Function	What It Returns	Great For
GETDATE()	Current date and time (server time)	CreatedAt, UpdatedAt
SYSDATETIME()	Current date and time with higher precision	Timestamps needing millisecond precision
GETUTCDATE()	Current UTC date and time	Global apps storing UTC timestamps
NEWID()	New unique GUID	Unique identifier columns
CURRENT_USER	The user running the insert	Audit columns like CreatedBy

Common Mistakes

Mistake 1: Passing NULL and expecting the default. The default only fires when the column is omitted from the INSERT, not when you explicitly send NULL.

Mistake 2: Skipping the constraint name. Unnamed defaults get system-generated names and are painful to drop later. Always name your constraints.

Mistake 3: Forgetting WITH VALUES. When you add a new column with a default to a table that already has rows, without WITH VALUES the existing rows stay NULL.

Mistake 4: Adding a default to a column that is already NULL. The default applies only to new rows; existing NULL values are not touched. Run an UPDATE if you want to clean them up.

Mistake 5: Using complex subqueries as a default. Defaults can use simple expressions and functions like GETDATE(), but not SELECT from other tables. For that, use a trigger or handle it in application code.

The Interview Answer

If an interviewer asks "What is a DEFAULT constraint?":

"A DEFAULT constraint is a rule on a column that fills in a predefined value when an INSERT does not supply one. It only applies when the column is omitted from the insert — if you explicitly pass NULL, the default is ignored. You can add it at table creation, alter it in later, add a new column with a default using WITH VALUES, and drop it easily if you gave it a clear name."

Summary

A DEFAULT constraint is one of SQL Server's simplest features — and also one of the most effective for cleaner code. Attach it to any column you want to fill in automatically when no value is passed. Use meaningful constraint names like DF_Users_Role so you can drop or modify them later without pain.

Remember the two rules that trip most beginners: (1) the default only triggers when the column is missing from the INSERT, not when it is explicitly NULL, and (2) when adding a new column with a default to an existing table, include WITH VALUES if you want old rows filled in. Master these small rules and your table design will be cleaner, safer, and far easier to maintain.

Concept	Key Takeaway
DEFAULT constraint	Auto-fills a value when INSERT omits the column
Triggering rule	Fires only when column is missing, not on explicit NULL
Naming	Always name it, e.g. DF_Users_Role
Add at creation	CONSTRAINT ... DEFAULT value inside CREATE TABLE
Add later	ALTER TABLE ADD CONSTRAINT ... DEFAULT value FOR col
New column + default	Use WITH VALUES to backfill existing rows
DEFAULT keyword	Force the default in an INSERT or UPDATE
Drop	ALTER TABLE DROP CONSTRAINT name
Common defaults	GETDATE(), NEWID(), constants, 0, 1
Interview line	"Default fills missing values, not explicit NULLs"