How to Use DEFAULT Constraints to Avoid NULL Insert Errors in SQL Server

When working with SQL databases like SQL Server, one common headache that can rear its ugly head from time to time is handling NULL values during inserts. Sometimes you just want a column to have a sensible default value if nothing is provided, without throwing errors or forcing developers to remember to include it every time. That’s where DEFAULT constraints come in.

A DEFAULT constraint automatically inserts a predefined value into a column when no explicit value is provided. This helps ensure consistency, prevents unwanted NULLs, and makes inserts cleaner.

Why You Might Need a `DEFAULT` Constraint

Imagine you have a table tracking warehouse inventory. Every time new stock arrives, you insert a record. Some columns (like the quantity and date for example) are required and so they can’t be NULL.

One option is to have the developer write code that inserts a default value when no explicit value is provided, but that quickly becomes repetitive and error-prone. With a DEFAULT constraint, SQL Server can handle those cases automatically.

The main benefits of using DEFAULT constraints include:

Avoids NULL insert errors on NOT NULL columns.
Reduces boilerplate code in your insert statements.
Keeps data consistent with predefined rules.

Example

Let’s walk through an example that you can run directly in SQL Server.

To really drive the point home, we’ll create a table with three DEFAULT constraints:

CREATE TABLE Inventory (
    ItemID INT IDENTITY PRIMARY KEY,
    ItemName NVARCHAR(100) NOT NULL,
    Quantity INT NOT NULL CONSTRAINT DF_Inventory_Quantity DEFAULT (0),
    ReceivedDate DATETIME NOT NULL CONSTRAINT DF_Inventory_ReceivedDate DEFAULT (GETDATE()),
    IsActive BIT NOT NULL CONSTRAINT DF_Inventory_IsActive DEFAULT (1)
);

Now let’s insert a row. We’ll start by inserting all values (i.e. no defaults will be used):

INSERT INTO Inventory (ItemName, Quantity, ReceivedDate, IsActive)
VALUES ('Steel Bolts', 500, '2025-09-01', 1);

Now we’ll insert another row, but this time we’ll omit the last three values (i.e. the ones that have DEFAULT constraints):

INSERT INTO Inventory (ItemName)
VALUES ('Copper Wire');

Now let’s check the result:

SELECT * FROM Inventory;

Result:

ItemID  ItemName     Quantity  ReceivedDate              IsActive
------  -----------  --------  ------------------------  --------
1       Steel Bolts  500       2025-09-01T00:00:00.000Z  true    
2       Copper Wire  0         2025-09-06T03:12:11.790Z  true

We can see that both insert operations succeeded, even though we didn’t provide all data for the second insert.

An important thing to remember is that the columns with the DEFAULT constraints are NOT NULL columns. Therefore, if we hadn’t set up the DEFAULT constraints, we would’ve seen an error when we tried to run the second INSERT statement.

Why You Might Need a DEFAULT Constraint

Example

Why You Might Need a `DEFAULT` Constraint