How to Set a Default Value for a Column in SQL

Add a Default Value

Write a SQL query to assign a default value to a column in an existing table.

Solution:

-- Set a default value of 0 for the "Salary" column.
ALTER TABLE Employees
ALTER COLUMN Salary SET DEFAULT 0; -- Assign a default value of 0 to "Salary".

Explanation:

1. Purpose of the Query :

1. The goal is to assign a default value of 0 to the Salary column in the Employees table.
2. This demonstrates how to use the ALTER TABLE statement to modify a column and add a default value.

2. Key Components :

1. ALTER TABLE Employees : Specifies the table to be modified.
2. ALTER COLUMN Salary : Indicates that the Salary column is being altered.
3. SET DEFAULT 0 : Assigns a default value of 0 to the Salary column.

3. Why use a Default Value? :

1. A default value ensures that if no value is explicitly provided for the Salary column during an INSERT operation, the database will automatically assign the default value (0 in this case).
2. This is useful for maintaining consistency and avoiding null values in columns where a fallback value is appropriate.

4. Real-World Application :

1. For example, in a company database, you might want to ensure that new employees have a default salary of 0 until their actual salary is determined and entered into the system.

Additional Notes:

• Default values are part of ensuring data integrity and consistency.
• Scenarios where default values are beneficial, such as:
• Ensuring that columns have a fallback value when no explicit value is provided.
• Avoiding null values in numeric or string columns where nulls could cause issues in calculations or queries.
• Important Considerations :
• Adding a default value does not affect existing rows unless explicitly updated.
• Ensure that the default value aligns with the business logic and expected behavior of the application.

For more Practice: Solve these Related Problems:

• Write a SQL query to assign a default value of 0 to the balance column in the accounts table.
• Write a SQL query to set a default value of 'Unknown' for the country column in the addresses table.
• Write a SQL query to assign a default value of CURRENT_TIMESTAMP to the updated_at column in the products table.
• Write a SQL query to set a default value of TRUE for the is_active column in the users table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: How to Rename a Table in SQL for Clearer Structure.
Next SQL Exercise: How to Remove a Constraint from an Existing SQL Table.