SQL - Default Constraint

SQL - The DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when an INSERT INTO statement does not specify one. This is a powerful tool for ensuring data integrity, simplifying application logic, and making your database schema more robust.

Why Use a DEFAULT Constraint?

• Data Integrity: Guarantees that a column will not be NULL if a value is omitted, assuming the column is also NOT NULL.
• Simplify Inserts: Allows you to omit columns with default values from your INSERT statements, making your queries cleaner.
• Enforce Business Rules: Automatically set initial statuses (e.g., status VARCHAR(20) DEFAULT 'pending'), timestamps, or flags.

Defining a DEFAULT Constraint

Syntax (during table creation)

The most common way to add a DEFAULT constraint is when you first create the table.

CREATE TABLE table_name (
    column1 datatype CONSTRAINT constraint_name DEFAULT default_value,
    column2 datatype DEFAULT default_value,
    ...
);

The value provided to DEFAULT must match the column’s data type. For example, use a number for an INT column and a quoted string for a VARCHAR column.

Example: A Modern User Profile Table

Let’s create a Users table where we want to automatically record when a user account is created and assign a default subscription level.

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT, -- AUTO_INCREMENT is a common non-standard extension
    Username VARCHAR(50) NOT NULL UNIQUE,
    SubscriptionLevel VARCHAR(10) DEFAULT 'free',
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Using a function as a default
);

In this example:

• SubscriptionLevel will be ‘free’ if not specified.
• IsActive will be TRUE for new users.
• CreatedAt will automatically be set to the current time of insertion.

Now, let’s insert a new user, providing only the required Username.

INSERT INTO Users (Username) VALUES ('alex_jones');

If we query the table, we’ll see the default values have been applied:

UserID	Username	SubscriptionLevel	IsActive	CreatedAt
1	alex_jones	free	TRUE	2023-11-20 10:30:00

Managing DEFAULT Constraints on Existing Tables

You can also add or remove DEFAULT constraints from an existing table using the ALTER TABLE statement. The syntax can vary slightly between database systems.

Syntax: Adding a DEFAULT Constraint

Here is the syntax for PostgreSQL and standard SQL:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

And for MySQL:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Syntax: Dropping a DEFAULT Constraint

For PostgreSQL and standard SQL:

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

And for MySQL:

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

Best Practices and Common Pitfalls

• Use Functions for Dynamic Defaults: For values like creation timestamps, always use database functions (CURRENT_TIMESTAMP, NOW()) rather than hard-coding a static value.
• DEFAULT vs. NULL: A DEFAULT constraint prevents a column from being NULL only if a value is omitted. If a user explicitly inserts NULL, the value will be NULL (unless the column also has a NOT NULL constraint).
• Check for Compatibility: Ensure your default value is valid for the column’s data type and any other constraints (e.g., CHECK constraints).
• Schema Versioning: When adding, changing, or dropping DEFAULT constraints, treat it as a schema migration. Use a version control tool for your database schema (like Flyway or Liquibase) to track these changes, especially in a team environment.