SQL DEFAULT Constraint

 The DEFAULT constraint in SQL is used to provide a default value for a column when no value is specified during the insertion of a new record. This can help ensure that a column always has a valid value, even if the user or application does not provide one explicitly.

Syntax:

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

Or, when altering an existing table to add a DEFAULT constraint to a column:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

Key Points:

• The DEFAULT constraint is only used when inserting data into a table without specifying a value for the column.
• If you do not provide a value for a column with a DEFAULT constraint, the default value will be used.
• The default value can be a constant (e.g., a number, string, or date) or an expression (like a function call).

Examples:

1. Creating a table with a DEFAULT constraint

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    hire_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(10) DEFAULT 'active'
);

• In this example:
  • The hire_date column will default to the current date if no value is provided.
  • The status column will default to 'active' if no value is provided.

2. Inserting data without providing a value for a column with a DEFAULT value

INSERT INTO employees (id, name) 
VALUES (1, 'John Doe');

• In this case, the hire_date will be set to the current date and status will be set to 'active', as these are the default values.

3. Altering an existing table to add a DEFAULT constraint

ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'inactive';

• This changes the default value of the status column to 'inactive' if no value is provided during an insert.

Common Use Cases:

• Setting default values for optional columns: For example, if a column is optional but you want to make sure it defaults to a known value if not explicitly set, such as status defaulting to 'active'.
• Date columns: For columns that represent timestamps or creation dates, it's common to use DEFAULT CURRENT_TIMESTAMP or DEFAULT CURRENT_DATE.

Notes:

• A DEFAULT constraint can only be set for a column and it must be a constant expression (e.g., a literal value or a function).
• Some databases allow more complex expressions or even functions like CURRENT_TIMESTAMP as default values.

Let me know if you'd like to see more examples or need further clarification!