DEFAULT Constraint in SQL – Automatically Insert Values
Introduction
In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.
In this section, you'll learn the definitions, differences, and best use cases for each text data type.
1. CHAR (Fixed-Length String)
CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.
Features:
- Fixed length
- Fast and predictable performance
- Uses extra storage if the data is often shorter than the specified length
Syntax:
column_name CHAR(length);
length = number of characters (1 to 255 depending on the database system)
Example:
CREATE TABLE countries (
country_code CHAR(2),
country_name CHAR(50)
);
country_code like 'US', 'IN', 'UK' will always take 2 characters.
When to Use CHAR:
- Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
- Fixed-format fields like credit card types ('VISA', 'MC')
- When exact storage size is known and consistent
2. VARCHAR (Variable-Length String)
VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.
Features:
- Variable length
- More space-efficient than CHAR for varying-length text
- Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)
Syntax:
column_name VARCHAR(length);
length = maximum number of characters allowed
Example:
CREATE TABLE employees (
first_name VARCHAR(50),
email VARCHAR(100)
);
Names and emails can vary in length, making VARCHAR ideal.
When to Use VARCHAR:
- Data with unpredictable or variable length
- Names, emails, addresses, and descriptions under 255-65535 characters
- Most general-purpose text fields
3. TEXT (Large Text Field)
TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.
Features:
- Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
- Cannot have a default value (in some databases like MySQL)
- TEXT fields are stored outside the main table with a pointer reference
- Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes
Syntax:
column_name TEXT;
Example:
CREATE TABLE articles (
id INT,
title VARCHAR(255),
body TEXT
);
body will store the full article content, which can be very large.
When to Use TEXT:
- Long-form text fields (comments, articles, reviews, reports)
- Data that exceeds normal VARCHAR limits
- When exact storage requirements are unknown or potentially very large
Quick Comparison: CHAR vs VARCHAR vs TEXT
Feature | CHAR | VARCHAR | TEXT |
---|---|---|---|
Storage | Fixed length | Variable length | Variable, large storage |
Max Size | Up to 255 chars | 65,535 bytes (typically) | 65,535+ chars (depends on type) |
Performance | Fast for fixed-size | Efficient for variable text | Slightly slower for queries |
Indexing | Full index support | Full index support | Limited in some DBs |
Best Use Case | Codes, fixed formats | Names, addresses, emails | Articles, long descriptions |
Important Tips
- Use CHAR only when all values will be exactly the same length
- VARCHAR is the best choice for most standard text fields
- Reserve TEXT for content that exceeds VARCHAR limits
- Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
- Be aware that TEXT fields may have limitations on default values and full-text indexing
What Is the DEFAULT Constraint?
The DEFAULT constraint in SQL allows you to automatically assign a default value to a column if no value is specified during an INSERT operation. This ensures consistency and saves time when dealing with repetitive or optional data.
Why Use DEFAULT?
To automatically assign standard values
To simplify insert queries
To ensure that columns are never empty unless intended
Common use cases include:
- Setting a default user role
- Assigning a default date like CURRENT_DATE
- Initializing counters or statuses
Syntax of DEFAULT in SQL
1. Define DEFAULT While Creating Table
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
status VARCHAR(20) DEFAULT 'active',
signup_date DATE DEFAULT CURRENT_DATE
);
In this case:
- If status is not provided, it defaults to 'active'
- If signup_date is not given, it defaults to the current date
2. Insert Without Specifying Column
INSERT INTO users (id, username)
VALUES (1, 'john_doe');
This will automatically insert:
- status = 'active'
- signup_date = CURRENT_DATE
3. Add DEFAULT to Existing Column
ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'active';
To remove a default value:
ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;
Note: Syntax may vary slightly depending on the database system (MySQL, PostgreSQL, SQL Server, etc.)
DEFAULT with Other Data Types
Data Type | Example Default Value |
---|---|
INT | DEFAULT 0 |
VARCHAR | DEFAULT 'pending' |
DATE | DEFAULT CURRENT_DATE |
BOOLEAN | DEFAULT TRUE or DEFAULT FALSE |
DEFAULT vs NULL
Feature | DEFAULT | NULL |
---|---|---|
Automatically assigned | Yes | No |
Requires value | Only if not provided manually | Can be set explicitly |
Purpose | Provide fallback values | Represent missing data |