SQL Tutorial

✨ SQL DEFAULT Constraint

The DEFAULT constraint in SQL is used to assign a default value to a column when no value is provided during an INSERT operation. This ensures that the column always has a value, even if no explicit value is specified.

1️⃣ Creating a Table with a DEFAULT Constraint

You can use the DEFAULT constraint while creating a table to specify a default value for a column when no value is supplied.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10, 2) DEFAULT 100
);

In this example, if the price is not specified during insertion, it will automatically be set to 100.

2️⃣ Inserting Data with the DEFAULT Value

When you insert data into a table with a DEFAULT constraint, you can either specify a value for the column or let the default value be used automatically.

INSERT INTO products (product_id, name)
VALUES (1, 'Laptop');

In this example, the price is not provided, so it will default to 100. You can check the inserted data with a SELECT query.

3️⃣ Using the DEFAULT Keyword in UPDATE Statements

You can also use the DEFAULT keyword in an UPDATE statement to reset a column's value back to its default.

UPDATE products
SET price = DEFAULT
WHERE product_id = 1;

This statement will reset the price of the product with product_id 1 back to its default value of 100.

4️⃣ Multiple DEFAULT Constraints in a Table

You can assign DEFAULT values to multiple columns in the same table, making sure that each column has a valid default when no value is specified.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(100),
  status VARCHAR(20) DEFAULT 'active',
  last_login DATETIME DEFAULT CURRENT_TIMESTAMP
);

In this example:

• The status column defaults to 'active'.
• The last_login column defaults to the current timestamp when a new row is inserted.

🔧 Key Considerations

• NULL Values: If a column has a DEFAULT value, it will only be used if no value is provided. If a value is explicitly provided (including NULL), that value will override the default.
• Implicit Defaults: Some databases, like MySQL, automatically use DEFAULT for certain data types (like empty strings or zero for numeric columns) when no value is provided.
• Multiple Defaults: You can apply default values to more than one column in a table, providing a fallback value for each column.