Meritshot Tutorials

  1. Home
  2. »
  3. SQL DEFAULT Constraint
SQL

SQL Tutorial

SQL DEFAULT Constraint

The DEFAULT constraint in SQL specifies a default value for a column when no value is provided during an INSERT operation. This ensures that a column always has a valid value, even if the user does not explicitly provide one. It helps maintain consistency and can simplify data entry operations.

Syntax

CREATE TABLE table_name (

    column_name datatype DEFAULT default_value,

    …

);

— Adding DEFAULT constraint to an existing column

ALTER TABLE table_name

ALTER COLUMN column_name SET DEFAULT default_value;

  • column_name: The name of the column to which the DEFAULT constraint is applied.
  • datatype: The type of data the column will store (e.g., INT, VARCHAR).
  • default_value: The default value assigned to the column if no other value is specified.

Example:

1 Creating a Table with a DEFAULT Constraint

Suppose you want to create a table Employees where the Status column should default to ‘Active’ if no status is provided:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FullName VARCHAR(100),

    Status VARCHAR(20) DEFAULT ‘Active’

);

In this table, if no value is provided for the Status column during insertion, it will automatically be set to ‘Active’.

2 Inserting Data with Default Values

When inserting a new row into the Employees table without specifying the Status, it will use the default value:

— Inserting with default Status

INSERT INTO Employees (EmployeeID, FullName)

VALUES (1, ‘Anil Patel’);

— Inserting with specified Status

INSERT INTO Employees (EmployeeID, FullName, Status)

VALUES (2, ‘Rajesh Kumar’, ‘Inactive’);

In the first insertion, Status will default to ‘Active’. In the second insertion, the Status value is explicitly provided as ‘Inactive’.

3 Adding a DEFAULT Constraint to an Existing Column

If you have an existing table Orders and want to set a default value for the ShippingCost column:

ALTER TABLE Orders

ALTER COLUMN ShippingCost SET DEFAULT 0.00;

This command sets the default ShippingCost to 0.00 for new rows where no shipping cost is provided.

4 Removing a DEFAULT Constraint

To remove a DEFAULT constraint from a column:

ALTER TABLE Orders

ALTER COLUMN ShippingCost DROP DEFAULT;

This command removes the default value, so the column will no longer use a default value if one is not provided.

Tips to Remember

  1. Ensure Valid Defaults: Make sure the default value is valid for the column’s datatype and constraints.
  2. Simplify Data Entry: Use default values to simplify data entry and ensure that columns have meaningful data even when not explicitly set.
  3. Consider Implications: Changing or removing default values can affect existing data and application behavior.

Frequently Asked Questions

Q1: Can I use expressions as default values?
A1: Yes, you can use expressions, such as CURRENT_DATE, as default values. For example, DATE DATE DEFAULT CURRENT_DATE.

Q2: Can the default value be NULL?
A2: Yes, you can set a default value of NULL, but this might be redundant if the column already allows NULL values.

Q3: What happens if I specify a default value for a column but then provide a value during insertion?
A3: The provided value will override the default value for that insertion.

Q4: How do I set a default value for a column that already has data?
A4: Set the default value using the ALTER TABLE command. Existing rows will not be affected by the new default value, but new rows will use it.

Q5: Can I set a default value for columns with CHECK constraints?
A5: Yes, default values can be used in conjunction with CHECK constraints, as long as they satisfy the condition imposed by the constraint.

❮ Previous
Next ❯
ads website