When defining tables in SQL, you often want certain columns to have a default value if no value is provided during an insert operation. The DEFAULT keyword allows you to specify a value that will automatically be used for a column when an explicit value is not supplied. This helps ensure data integrity and consistency, since new rows will always have meaningful values in these columns unless you intentionally override them.

CREATE TABLE user_accounts (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at DATE DEFAULT CURRENT_DATE
);

In this example, the status column will automatically be set to 'active' if you do not specify a value when inserting a new user. Similarly, the created_at column will be set to the current date using the CURRENT_DATE function unless you provide a different date. By including default values in your table definition, you make your data model more robust and reduce the risk of missing or inconsistent data.

When you insert a new row into a table and omit columns that have default values, SQL will automatically fill in those columns with their specified defaults. This means you can write shorter and cleaner INSERT statements without worrying about every single column, as long as those columns have sensible defaults defined.

-- Insert a user, omitting both status and created_at
INSERT INTO user_accounts (username) VALUES ('jdoe');

-- Insert a user, specifying status but omitting created_at
INSERT INTO user_accounts (username, status) VALUES ('asmith', 'pending');

-- View the resulting data
SELECT * FROM user_accounts;

In the first INSERT, only the username is provided, so status defaults to 'active' and created_at defaults to the current date. In the second INSERT, the status is set to 'pending', but created_at still defaults to the current date. This behavior helps you avoid errors and keeps your table data predictable, as every new row will have all required values, either from the provided data or from the defaults you have established.