Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

NUll vs NOT NULL

In SQL Server, NULL and NOT NULL are constraints used to define whether a column in a table can store null values. A NULL value represents missing or unknown data, while a NOT NULL constraint ensures that a column must have a value and cannot be left empty. Choosing between NULL and NOT NULL is crucial for maintaining data integrity and ensuring that essential data is always present.

Key Concepts

1. NULL

A column defined as NULL can store null values, which indicates that the data is absent or unknown.
Use NULL when it is acceptable for the column not to have a value.

2. NOT NULL

A column defined as NOT NULL must contain a value; it cannot be left empty.
Use NOT NULL for columns that require a value for data integrity and business logic.

3. Default Values

Columns defined as NOT NULL can also have default values, which are used if no value is provided during data insertion.

4. Impact on Queries

NULL values can affect query results and operations, such as aggregations and comparisons. Special functions and operators like IS NULL and IS NOT NULL are used to handle NULL values.

Code Samples

1. Creating Tables with NULL and NOT NULL Columns

Define columns with NULL and NOT NULL constraints.

CREATE TABLE Store(
    StoreID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, cannot be NULL
    StoreName VARCHAR(100) NOT NULL, -- Store name, cannot be NULL
    Location VARCHAR(100) NULL, -- Location can be NULL
    OpenDate DATETIME -- OpenDate can be NULL by default
);

CREATE TABLE Products(
    ProductID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, cannot be NULL
    ProductName VARCHAR(100) NOT NULL, -- Product name, cannot be NULL
    Category VARCHAR(50) NULL, -- Category can be NULL
    Price DECIMAL(10,2) NOT NULL, -- Price cannot be NULL
    StockQuantity INT NULL -- StockQuantity can be NULL
);

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, cannot be NULL
    FirstName VARCHAR(50) NOT NULL, -- First name, cannot be NULL
    LastName VARCHAR(50) NOT NULL, -- Last name, cannot be NULL
    Email VARCHAR(100) NULL, -- Email can be NULL
    PhoneNumber VARCHAR(15) NULL -- Phone number can be NULL
);

CREATE TABLE Sales(
    SaleID INT PRIMARY KEY IDENTITY(1,1), -- Primary key, cannot be NULL
    SaleDate DATETIME NOT NULL, -- SaleDate cannot be NULL
    StoreID INT NOT NULL, -- Foreign key, cannot be NULL
    ProductID INT NOT NULL, -- Foreign key, cannot be NULL
    CustomerID INT NULL, -- CustomerID can be NULL
    Quantity INT NOT NULL, -- Quantity cannot be NULL
    TotalAmount DECIMAL(10,2) NOT NULL -- TotalAmount cannot be NULL
);

Combined Code Sample

Here is all the code combined for easier reference:

-- Creating Tables with NULL and NOT NULL Columns

CREATE TABLE Store(
    StoreID INT PRIMARY KEY IDENTITY(1,1),
    StoreName VARCHAR(100) NOT NULL,
    Location VARCHAR(100) NULL,
    OpenDate DATETIME
);

CREATE TABLE Products(
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50) NULL,
    Price DECIMAL(10,2) NOT NULL,
    StockQuantity INT NULL
);

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NULL,
    PhoneNumber VARCHAR(15) NULL
);

CREATE TABLE Sales(
    SaleID INT PRIMARY KEY IDENTITY(1,1),
    SaleDate DATETIME NOT NULL,
    StoreID INT NOT NULL,
    ProductID INT NOT NULL,
    CustomerID INT NULL,
    Quantity INT NOT NULL,
    TotalAmount DECIMAL(10,2) NOT NULL
);

These examples should help you understand how to use NULL and NOT NULL constraints effectively in your SQL Server tables. Feel free to adapt these definitions based on your specific requirements.

Tansy SQL Course - NUll vs NOT NULL - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found