Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

DEFAULT Constraint

The DEFAULT constraint in SQL Server is used to set a default value for a column if no explicit value is provided during data insertion. This is useful for ensuring that columns always have a value, which can be critical for maintaining data integrity and consistency. By using DEFAULT, you can avoid issues with missing values and simplify your data entry process.

Key Concepts

Purpose of DEFAULT Constraint
- Provides a default value for a column if no value is specified.
- Helps in maintaining data consistency by ensuring that columns always have a value.
Syntax
- DEFAULT default_value: Defines the default value for a column.
Adding DEFAULT Constraints
- You can add DEFAULT constraints when creating a table or modify an existing table to include a DEFAULT constraint.
Default Value Types
- Default values can be literals (e.g., numbers, strings) or expressions (e.g., GETDATE() for current date and time).

Code Samples

1. Creating Tables with `DEFAULT` Constraints

Define tables with various DEFAULT constraints to provide default values for columns.

CREATE TABLE Store(
    StoreID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
    StoreName VARCHAR(100) NOT NULL DEFAULT 'Unknown', -- Default value if no name is provided
    Location VARCHAR(100) DEFAULT 'Not Specified', -- Default location
    OpenDate DATETIME DEFAULT GETDATE() -- Default to current date and time
);

CREATE TABLE Products(
    ProductID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
    ProductName VARCHAR(100) NOT NULL, -- No default value required
    Category VARCHAR(50) DEFAULT 'General', -- Default category if not provided
    Price DECIMAL(10, 2) NOT NULL DEFAULT 0.00, -- Default price if not provided
    StockQuantity INT DEFAULT 0 -- Default stock quantity
);

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
    FirstName VARCHAR(50) NOT NULL, -- No default value required
    LastName VARCHAR(50) NOT NULL, -- No default value required
    Email VARCHAR(100) DEFAULT 'Not Provided', -- Default email if not provided
    PhoneNumber VARCHAR(15) DEFAULT 'Not Provided' -- Default phone number
);

CREATE TABLE Sales(
    SaleID INT PRIMARY KEY IDENTITY(1,1), -- Primary key
    SaleDate DATETIME NOT NULL DEFAULT GETDATE(), -- Default to current date and time
    StoreID INT NOT NULL, -- Foreign key, no default value
    ProductID INT NOT NULL, -- Foreign key, no default value
    CustomerID INT NULL DEFAULT NULL, -- Default to NULL if no customer ID is provided
    Quantity INT NOT NULL DEFAULT 1, -- Default quantity
    TotalAmount DECIMAL(10, 2) DEFAULT 0.00 -- Default total amount
);

These examples illustrate how to use the DEFAULT constraint to provide default values for columns in your SQL Server tables. Adjust these examples based on your specific needs to ensure that your data entries are consistent and complete.

Tansy SQL Course - DEFAULT Constraint - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found

Microsoft SQL Server

DEFAULT Constraint

Key Concepts

Code Samples

1. Creating Tables with DEFAULT Constraints

Most visited

1. Creating Tables with `DEFAULT` Constraints