SQL DEFAULT Keyword

 The DEFAULT keyword in SQL is used to provide a default value for a column when no value is specified during an INSERT operation. This can be helpful when you want to ensure that a column always has a valid value, even if the user doesn't explicitly provide one.

Key Points about DEFAULT:

1. Default Value: When a new record is inserted into a table, if the value for a column is not provided, the default value is inserted automatically. The default can be a static value (e.g., 0, 'N/A') or a function (e.g., CURRENT_DATE for a date column).

2. Setting Default Value: The default value is defined when creating or altering the table schema.

3. No Value Inserted: When inserting a row, if the column is not included in the INSERT statement, the default value is used.

Syntax for DEFAULT in SQL:

1. During Table Creation:

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

Example:

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    hire_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'active'
);

2. Modifying a Table to Add a Default Value:

ALTER TABLE table_name
MODIFY column_name datatype DEFAULT default_value;

Example:

ALTER TABLE employees
MODIFY status VARCHAR(20) DEFAULT 'active';

3. Inserting Data Using Default:

If a column has a default value and the value is not provided during an insert, the default is used.

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

In this example, since hire_date and status are not included, the default values (CURRENT_DATE for hire_date and 'active' for status) are used.

Example Table with Default Values:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

When inserting data into the products table:

INSERT INTO products (product_id, product_name) 
VALUES (1, 'Product A');

The price will default to 0.00, and created_at will default to the current timestamp.

When DEFAULT is Not Allowed:

• A column that is part of a NOT NULL constraint must have a DEFAULT if it is not included in the INSERT statement.
• If a column does not allow NULL and does not have a DEFAULT, an error will occur during the INSERT.

Changing Default Values:

You can change the default value of an existing column using ALTER TABLE.

ALTER TABLE products 
ALTER COLUMN price SET DEFAULT 10.00;

This command changes the default value of the price column to 10.00 for future inserts.

Summary:

• The DEFAULT keyword provides a value for columns when no explicit value is provided during insertion.
• It can be used to simplify the insertion process by automatically filling in default values.
• Default values can be set for any column type, including numbers, strings, dates, and timestamps.

Let me know if you'd like more details on any specific aspect!