How to Set a Column with Default Value in MySQL

In MySQL, setting a default value for a column ensures that a predefined value is inserted into the column when no value is provided during an insert operation. This can be useful in cases where a column should have a common default value such as a status or a date.

Step 1: Creating a Table with a Default Column Value

Let’s begin by creating a table that includes a column with a default value. Below is the SQL syntax for creating such a table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    status VARCHAR(10) DEFAULT 'active'
);
                                

In the example above, the status column is given a default value of 'active'. This means if no value is provided for the status column during an insert, it will automatically be set to 'active'.

Step 2: Inserting Data Without Specifying the Default Column

Now, let’s see how this works when we insert data into the users table:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
                                

In this case, the status column will automatically be set to 'active', as specified in the table definition.

Step 3: Altering an Existing Table to Add a Default Value

If you have an existing table and want to add a default value to an existing column, you can use the ALTER TABLE statement. Below is an example of how to add a default value to an existing column:

ALTER TABLE users
MODIFY status VARCHAR(10) DEFAULT 'inactive';
                                

After running the above command, any new rows inserted into the table without specifying a value for status will default to 'inactive'.

Step 4: Setting Default Values in Django Models

In Django, you can set default values for fields directly in your models using the default attribute. Here’s an example of how to define a default value in a Django model:

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField()
    status = models.CharField(max_length=10, default='active')
                                

In this Django model, the status field will have a default value of 'active', just like in the MySQL table.

Conclusion

Setting default values for columns in MySQL is a simple and effective way to ensure that your tables have predefined values when no input is provided. It’s particularly useful in cases where certain columns should always have a default value for consistency. With Django, you can easily mirror this behavior in your models by using the default parameter.