SQL Server Interview Question #37

What is a DEFAULT constraint?

Data Types, Keys & Constraints Junior Beginner

Detailed Explanation

A DEFAULT constraint supplies a value when an INSERT statement does not explicitly provide a value for the column.

Defaults are useful for properties such as creation timestamps, active flags, status values, and generated identifiers. A default does not override an explicitly supplied value, including an explicit NULL when NULL is allowed.

Code Example

CREATE TABLE BlogPosts
(
    Id INT IDENTITY PRIMARY KEY,
    Title NVARCHAR(200) NOT NULL,
    IsPublished BIT NOT NULL
        CONSTRAINT DF_BlogPosts_IsPublished DEFAULT 0,
    CreatedAt DATETIME2 NOT NULL
        CONSTRAINT DF_BlogPosts_CreatedAt DEFAULT SYSDATETIME()
);