SQL DEFAULT Constraint

What is a DEFAULT Constraint?

The DEFAULT constraint in SQL is used to provide a default value for a column when no value is specified during an insert operation. This ensures that the column always has a valid value, even if one is not explicitly provided.

Why Use DEFAULT Constraints?

Using DEFAULT constraints helps to prevent NULL values in columns where a value is expected, and it simplifies data entry by automatically providing a value if none is specified. This is particularly useful for columns like timestamps, status fields, or any field where a common default value is often required.

Creating a DEFAULT Constraint

You can define a DEFAULT constraint when creating a table or adding a column. The syntax for adding a DEFAULT constraint is as follows:

Example: Creating a DEFAULT Constraint on a New Table

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderDate date DEFAULT GETDATE(), 
    Status varchar(255) DEFAULT 'Pending'
);

In this example, the OrderDate column will default to the current date if no value is provided, and the Status column will default to 'Pending'.

Adding a DEFAULT Constraint to an Existing Table

If you need to add a DEFAULT constraint to an existing table, you can use the ALTER TABLE command:

ALTER TABLE Orders
ADD CONSTRAINT DF_Status DEFAULT 'Pending' FOR Status;

This command adds a DEFAULT constraint named DF_Status to the Status column of the Orders table.

Practical Use Cases for DEFAULT Constraints

Here are some common scenarios where a DEFAULT constraint might be useful:

• Timestamp columns: Automatically set the current date and time for a record when it’s created.
• Status fields: Default status for new records, such as 'Pending', 'New', or 'Active'.
• Boolean flags: Default a boolean column to TRUE or FALSE.
• Numeric defaults: Provide a default numeric value, such as 0 or 1.

Example: Using DEFAULT for a Boolean Column

CREATE TABLE Users (
    UserID int PRIMARY KEY,
    Username varchar(255) NOT NULL,
    IsActive bit DEFAULT 1
);

In this example, the IsActive column will default to 1 (which typically represents TRUE) if no value is provided, indicating that a new user is active by default.

Removing a DEFAULT Constraint

If you need to remove a DEFAULT constraint from a column, use the ALTER TABLE command:

ALTER TABLE Orders
DROP CONSTRAINT DF_Status;

This command removes the DEFAULT constraint named DF_Status from the Status column in the Orders table.

Conclusion

The DEFAULT constraint is a simple yet powerful tool for ensuring data consistency and reducing the need for manual data entry. By automatically providing values when none are specified, it helps prevent errors and ensures that your database remains consistent.