We can provide a default value for a column. If we don specify its value, it will automatically assign a default value.
What is the Default value in MySql
n MySQL, the default value is a predefined value that is assigned to a column if no explicit value is provided during the insertion of a new row.
It is a way to ensure that a column always has a value, even if it is not specified explicitly.
When a column is defined with a default value, if a new row is inserted into the table and the value for that column is not provided, the default value will be automatically assigned to the column.
Different Ways to specify default values
- The default value on table creation
- The default value on alter table
- IFNULL() function on Insert update Command
- Triggers
1. The syntax for adding default value
1 2 3 4 | CREATE TABLE table_name ( column_name data_type DEFAULT default_value, ... ); |
sql add column with default value in MySql
1 2 3 4 5 | CREATE TABLE users ( id INT, name VARCHAR(50), age INT DEFAULT 0 ); |
In the above example, if you insert a row into the “users” table without specifying a value for the “age” column, it will automatically be set to 0.
However, if you do provide a value for the “age” column during the insertion, that value will be used instead of the default.
2. MySql Example to add Default value
1 2 3 4 5 6 7 | create table product( id int(11) NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, quantity int(10) DEFAULT 100, price decimal(6,2) DEFAULT 10.22, status varchar(50) DEFAULT 'AVAILABLE', add_date timestamp DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id)); |
this will produce the following output
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> desc product; +----------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | quantity | int(10) | YES | | 100 | | | price | decimal(6,2) | YES | | 10.22 | | | status | varchar(50) | YES | | AVAILABLE | | | add_date | timestamp | NO | | CURRENT_TIMESTAMP | | +----------+--------------+------+-----+-------------------+----------------+ 6 rows in set (0.06 sec) |
while inserting any row in the table if we don’t provide a value for a column that has a default value that its default value will be inserted for example
1 | mysql> insert into product(name) values('Mobile'); |
We have inserted only the product name and the value of quantity, price, status, and add_date as the default value.
1 2 3 4 5 6 7 | mysql> select * from product; +----+--------+----------+-------+-----------+---------------------+ | id | name | quantity | price | status | add_date | +----+--------+----------+-------+-----------+---------------------+ | 1 | Mobile | 100 | 10.22 | AVAILABLE | 2017-04-05 17:10:42 | +----+--------+----------+-------+-----------+---------------------+ 1 row in set (0.00 sec) |
Alter Table command to Set default value in MySql
1 2 | ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value; |
1 2 | ALTER TABLE customers ALTER COLUMN city SET DEFAULT 'Unknown'; |
alter table add column default value
1 2 | ALTER TABLE users |
3. IFNULL() function on Insert update Command
- This method is useful when you want to add a default value to an already existing column.
- IFNULL() Function: The IFNULL() function can be used within an INSERT or UPDATE statement to provide a default value if the column value is NULL. Here’s an example:
IFNULL() Function Syntax to Insert Default value
1 2 | INSERT INTO table_name (column_name) VALUES (IFNULL(column_value, default_value)); |
1 2 | INSERT INTO table_name (column_name) VALUES (COALESCE(column_value, default_value)); |
If the column_value is NULL, the default_value will be used.
4. Triggers
Triggers allow you to execute custom logic when certain events occur, such as before or after an INSERT or UPDATE operation.
You can use triggers to set a default value for a column dynamically. Here’s a simplified example:
1 2 3 4 5 6 7 8 | CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN IF NEW.column_name IS NULL THEN SET NEW.column_name = default_value; END IF; END; |
while inserting data into a table, it means that you’re trying to insert a row without providing a value for a column that does not have a default value defined.
MySQL requires that you either provide a value for every column in the row you are inserting or define a default value for columns that allow NULL values.