NOT NULL & DEFAULT
NOT NULL and DEFAULT are SQL Server constraints used to control the values stored in table columns. NOT NULL prevents NULL values, while DEFAULT automatically provides a value when no value is supplied.
What is NOT NULL?
The NOT NULL constraint ensures that a column must always contain a value. A NULL value cannot be inserted into a NOT NULL column.
Simple Example
If StudentName is defined as NOT NULL, every student record must contain a StudentName.
Creating a NOT NULL Column
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
Age INT
);
NOT NULL Example Output
INSERT INTO Students
VALUES (101, NULL, 22);
What is DEFAULT?
The DEFAULT constraint automatically inserts a predefined value when a value is not provided for a column.
Simple Example
If City has a DEFAULT value of Pune, SQL Server will automatically insert Pune when City is not specified.
Creating a DEFAULT Constraint
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
City VARCHAR(50) DEFAULT 'Pune'
);
Using DEFAULT Value
When City is not specified, SQL Server automatically uses the default value.
INSERT INTO Students
(
StudentId,
StudentName
)
VALUES
(
101,
'Rahul'
);
Output
| StudentId | StudentName | City |
|---|---|---|
| 101 | Rahul | Pune |
Named DEFAULT Constraint
We can provide a specific name to a DEFAULT constraint.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100) NOT NULL,
City VARCHAR(50),
CONSTRAINT DF_Students_City
DEFAULT 'Pune' FOR City
);
Add DEFAULT to Existing Table
A DEFAULT constraint can be added to an existing table using ALTER TABLE.
ALTER TABLE Students
ADD CONSTRAINT DF_Students_City
DEFAULT 'Pune' FOR City;
NOT NULL vs DEFAULT
| NOT NULL | DEFAULT |
|---|---|
| Prevents NULL values. | Provides a default value. |
| Column must contain a value. | Value is automatically supplied when omitted. |
| Used for mandatory data. | Used for automatic default data. |
NOT NULL and DEFAULT Together
NOT NULL and DEFAULT can be used together when a column must always contain a value and should receive an automatic value when one is not supplied.
CREATE TABLE Employees
(
EmployeeId INT PRIMARY KEY,
EmployeeName VARCHAR(100) NOT NULL,
Department VARCHAR(50) DEFAULT 'IT'
);
NOT NULL & DEFAULT Structure
Column
|
├── NOT NULL
| ↓
| NULL Not Allowed
|
└── DEFAULT
↓
Automatic Value
↓
When Value
Is Not Provided
Important Points
- NOT NULL prevents NULL values.
- DEFAULT provides an automatic value when a value is not supplied.
- NOT NULL is useful for mandatory columns.
- NOT NULL and DEFAULT can be used together.
Summary
In this section, we learned about NOT NULL and DEFAULT constraints, how to create them, how to add them to an existing table and how they control values stored in SQL Server tables.