MySQL Add Column With Default Value - StackHowTo

In this tutorial, we are going to see how to add a column with a default value and how to add a default value to a column that already exists in a MySQL table.

Add Column With Default Value

When adding a column to a table using ALTER, we can also specify a default value. In the example below, by using ALTER, the column “Address” is added with a default value “New York” to “Employee” table. Alter table Employee ADD(Address Varchar(10) Default 'New York');

Now we can check the default value of the column ‘Address’, by running the following command.

DESCRIBE Employee;

Output:

+----------+-----------------+--------+------+----------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+--------+------+----------+----------------+ | EmpID | int<11> | No | PRI | NULL | auto_increment | | Age | int<10> | Yes | | NULL | | | Name | varchar<20> | Yes | | NULL | | | Address | varchar<10> | Yes | | New York | | +----------+-----------------+--------+------+----------+----------------+

Add a default value to a column that already exists

To add a default value to a column in MySQL, use the ALTER TABLE … ALTER COLUMN … SET DEFAULT statement. Here is an example:

ALTER TABLE Employee ALTER COLUMN age SET DEFAULT 0;

Now we can check the default value of the ‘age’ column, by running the following command.

DESCRIBE Employee;

Output:

+----------+-----------------+--------+------+----------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+--------+------+----------+----------------+ | EmpID | int<11> | No | PRI | NULL | auto_increment | | Age | int<10> | Yes | | 0 | | | Name | varchar<20> | Yes | | NULL | | | Address | varchar<10> | Yes | | New York | | +----------+-----------------+--------+------+----------+----------------+ MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More

• MYSQL MCQ with Answers – Part 1
• MYSQL MCQ with Answers – Part 2
• MySQL VERSION()
• MySQL GROUP_CONCAT()
• MySQL ISNULL() Function
• MySQL CONVERT()
• MySQL CAST()
• MySQL MD5()
• How To Get Year From Date In MySQL
• MySQL TIMESTAMP()
• MySQL TIMEDIFF()
• MySQL: How to Convert Seconds To HH:MM:SS Format
• How To Get Month From Date In MySQL
• MySQL NOW()
• MySQL – Get Day of Week Name Using DAYOFWEEK()
• MySQL – Get Difference Between Two Dates in Days
• MySQL DATE_FORMAT()
• MySQL ROUND()
• MySQL RAND()
• MySQL INSTR()
• MySQL LOCATE()
• MySQL LCASE()
• MySQL UCASE()
• MySQL String To Lowercase
• MySQL Uppercase in Where Clause
• MySQL Select Uppercase
• MySQL LPAD() With Leading Zeros
• MySQL RTRIM()
• MySQL LTRIM()
• MySQL Trim Whitespace Using TRIM() Function
• How to Reverse a String in MySQL
• MySQL RIGHT() Function
• MySQL LEFT() Function
• MySQL SUBSTRING() with Examples
• MySQL SOUNDEX() with Examples
• MySQL String Length
• MySQL Concatenate Multiple Columns
• MySQL Select SUM() with Example
• MySQL Select MAX()
• MySQL Select MIN()
• MySQL COUNT()
• MySQL Average AVG()
• How To Comment In MySQL
• How to Comment Multiple Lines in MySQL
• MySQL OPTIMIZE
• MySQL EXPLAIN
• MySQL CREATE INDEX with Example
• MySQL ANY with Example
• MySQL ALL with Example
• MySQL EXISTS
• MySQL NATURAL JOIN
• MySQL SELF JOIN
• MySQL FULL JOIN
• MySQL RIGHT JOIN
• MySQL LEFT JOIN
• MySQL CROSS JOIN
• MySQL AUTO_INCREMENT
• SQL UNION ALL
• SQL GROUP BY WITH ROLLUP
• SQL Wildcards in Statement
• SQL IN Operator
• SQL AND and OR Operators
• How to use SQL_NO_CACHE?
• MySQL SELECT INTO
• MySQL ON DELETE CASCADE
• MySQL DELETE
• MySQL WHERE
• AND Operator in MySQL
• OR Operator in MySQL
• MySQL IS NULL
• MySQL REPLACE
• MySQL INSERT INTO SELECT
• MySQL INSERT IGNORE
• MySQL INSERT ON DUPLICATE KEY UPDATE
• Derived table (subquery) with MySQL
• How to Insert Multiple Rows in MySQL at a Time
• MySQL UNION
• MySQL LIMIT
• Replace All NULL Values with 0 in MySQL
• MySQL UPDATE
• MySQL INSERT INTO
• CASE WHEN in MySQL with Multiple Conditions
• MINUS in MySQL
• INTERSECT in MySQL
• How to check if a record exists in another table in MySQL
• MySQL HAVING
• MySQL GROUP BY
• MySQL BETWEEN
• MySQL LIKE
• How to Check if Value Exists in a MySQL Database
• MySQL ORDER BY
• MySQL CONCAT()
• MySQL CREATE TABLE
• MySQL SELECT DISTINCT
• How to Use Column Alias in Select Clause – MySQL
• MySQL SELECT
• MYSQL MCQ with Answers – Part 3
• MYSQL MCQ with Answers – Part 4
• MYSQL MCQ with Answers – Part 5
• MYSQL MCQ with Answers – Part 6
• MYSQL MCQ with Answers – Query optimization
• MySQL Practice Exercises with Solutions – Part 1
• MySQL Practice Exercises with Solutions – Part 2
• MySQL Practice Exercises with Solutions – Part 3
• MySQL Practice Exercises with Solutions – Part 4
• MySQL Practice Exercises with Solutions – Part 5
• MySQL Practice Exercises with Solutions – Part 6
• MySQL Practice Exercises with Solutions – Part 7
• MySQL Practice Exercises with Solutions – Country Database – Part 8
• MySQL Practice Exercises with Solutions – Ordering System Database – Part 9
• How to Use a CASE-WHEN Statement in a MySQL Stored Procedure
• IF-THEN Condition in MySQL Stored Procedure
• How to Declare and Assign a Value to a Variable in MySQL Stored Procedure
• How to Create a Stored Procedure with Parameters in MySQL
• How to Drop a Stored Procedure in MySQL
• How to show all stored procedures/functions in MySQL
• How to Create a Stored Procedure in MySQL
• How to create composite primary key in MySQL PHPMyAdmin
• How to Create a Primary Key in MySQL
• How to Set up Multiple Fields as Primary Key in MySQL
• How to Set Primary Key and Auto_increment in PHPMyAdmin
• How to Export a MySQL Database using Command Line
• How to Import a MySQL Database using Command Line
• How to Export Database in phpMyAdmin
• How to Import Database in phpMyAdmin
• How to Create a Form in PHP MySQL
• PHP – Password Hash & Password Verify with Example
• How to Check if Username Already Exists in Database using PHP MySQL
• How to Check if Email Already Exists in Database using PHP
• How to Display Blob Image in PHP from Database
• How to call stored procedure in PHP with MySQLi
• PHP MySQL Transactions with Examples
• How to delete a row in MySQL using PHP
• How to copy data from one table to another in MySQL using PHP
• How to update data in MySQL database using PHP PDO
• How to insert multiple rows in MySQL using PHP
• How to insert data in MySQL using PHP PDO
• How to Fetch Data from Database in PHP and Display in HTML Table using PDO
• How to Connect to MySQL Database in PHP using PDO
• How to Create a MySQL Table with PDO in PHP
• MySQL INNER JOIN with Examples
• How to Delete a View in MySQL
• How to Update a View in MySQL
• How to Create a View in MySQL
• How to Remove Default Value from Column in MySQL
• MySQL Add Column With Default Value
• How to Drop NOT NULL Constraint in MySQL
• How to Add NOT NULL Constraint in MySQL using ALTER Command
• How to Change Auto Increment Value in MySQL
• MySQL Sequence
• How to Remove a Primary Key in MySQL
• How to create index for existing table in MySQL
• How to Drop Index From Table in MySQL
• How to delete all rows from a table in MySQL
• How to Delete Table in MySQL
• How to Delete a Column in a Table in MySQL
• How to Rename a Table in MySQL
• How to Change Column Name in MySQL
• How to Change the Data Type for a Column in MySQL
• How to Add a Column in a Table in MySQL
• How to drop a temporary table in MySQL
• How to Create a Temporary Table in MySQL
• Primary Key and Foreign Key Examples
• How to Create Table in MySQL Command Line
• How to Delete Database in MySQL
• How to check the version of MySQL in Windows
• How to Create Database in MySQL
• How to install MySQL Workbench on Ubuntu using Terminal
• How to install MySQL on CentOS 7
• How to install MySQL on Ubuntu
• How to install MySQL on Windows
• List of MySQL Commands with Examples