{"id":10403,"date":"2021-07-25T21:18:07","date_gmt":"2021-07-26T04:18:07","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-default\/"},"modified":"2021-07-25T23:10:11","modified_gmt":"2021-07-26T06:10:11","slug":"mysql-default","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-default\/","title":{"rendered":"MySQL DEFAULT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about MySQL DEFAULT constraint and how to use it effectively.<\/p>\n\n\n\n

Introduction to the MySQL DEFAULT constraint<\/h2>\n\n\n\n

MySQL DEFAULT<\/code> constraint allows you to specify a default value for a column. Here’s the syntax of the DEFAULT<\/code> constraint:<\/p>\n\n\n

column_name data_type DEFAULT default_value;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the DEFAULT<\/code> keyword followed by the default value for the column. The type of the default value matches the data type of the column.<\/p>\n\n\n\n
The default_value<\/code> must be a literal constant, e.g., a number or a string. It cannot be a function or an expression. However, MySQL allows you to set the current date and time (CURRENT_TIMESTAMP<\/code>) to the TIMESTAMP<\/a><\/code> and DATETIME<\/a><\/code> columns.<\/p>\n\n\n\n
When you define a column without the NOT NULL<\/a><\/code> constraint, the column will implicitly take NULL<\/code> as the default value.<\/p>\n\n\n\n
If a column has a DEFAULT<\/code> constraint and the INSERT<\/a><\/code> or UPDATE<\/a><\/code> statement doesn’t provide the value for that column, MySQL will use the default value specified in the DEFAULT<\/code> constraint.<\/p>\n\n\n\n
Typically, you set the DEFAULT<\/code> constraints for columns when you create the table<\/a>. MySQL also allows you to add default constraints to the columns of existing tables. If you don’t want to use default values for columns, you can remove the default constraints.<\/p>\n\n\n\n
MySQL DEFAULT constraint example<\/h2>\n\n\n\n
The following example creates a new table named cart_items<\/code> with four columns item_id<\/code>, name<\/code>, quantity<\/code>, and sales_tax<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> cart_items \n(\n    item_id INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    quantity INT<\/span> NOT<\/span> NULL<\/span>,\n    price DEC<\/span>(5<\/span>,2<\/span>) NOT<\/span> NULL<\/span>,\n    sales_tax DEC<\/span>(5<\/span>,2<\/span>) NOT<\/span> NULL<\/span> DEFAULT<\/span> 0.1<\/span>,\n    CHECK<\/span>(quantity > 0<\/span>),\n    CHECK<\/span>(sales_tax >= 0<\/span>) \n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The sales_tax<\/code> column has a default value 0.1 (10%). The following statement shows the cart_items<\/code> table:<\/p>\n\n\n
DESC cart_items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------+--------------+------+-----+---------+----------------+\n| Field     | Type         | Null | Key | Default | Extra          |\n+-----------+--------------+------+-----+---------+----------------+\n| item_id   | int          | NO   | PRI | NULL    | auto_increment |\n| name      | varchar(255) | NO   |     | NULL    |                |\n| quantity  | int          | NO   |     | NULL    |                |\n| price     | decimal(5,2) | NO   |     | NULL    |                |\n| sales_tax | decimal(5,2) | NO   |     | 0.10    |                |\n+-----------+--------------+------+-----+---------+----------------+\n5 rows in set (0.01 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following INSERT<\/code> statement adds a new item to the cart_items<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> cart_items(name<\/span>, quantity, price)\nVALUES<\/span>('Keyboard'<\/span>, 1<\/span>, 50<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the INSERT<\/code> statement doesn’t provide a value for the sales_tax<\/code> column. The sales_tax<\/code> column useS the default value specified in the DEFAULT<\/code> constraint:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> cart_items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------+----------+----------+-------+-----------+\n| item_id | name     | quantity | price | sales_tax |\n+---------+----------+----------+-------+-----------+\n|       1 | Keyboard |        1 | 50.00 |      0.10 |\n+---------+----------+----------+-------+-----------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Also, you can explicitly use the DEFAULT<\/code> keyword when you insert a new row into the cart_items<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> cart_items(name<\/span>, quantity, price, sales_tax)\nVALUES<\/span>('Battery'<\/span>,4<\/span>, 0.25<\/span> , DEFAULT<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the sales_tax<\/code> column takes the default value:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> cart_items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------+----------+----------+-------+-----------+\n| item_id | name     | quantity | price | sales_tax |\n+---------+----------+----------+-------+-----------+\n|       1 | Keyboard |        1 | 50.00 |      0.10 |\n|       2 | Battery  |        4 |  0.25 |      0.10 |\n+---------+----------+----------+-------+-----------+\n2 rows in set (0.01 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Adding a DEFAULT constraint to a column<\/h2>\n\n\n\n
To add a default constraint to a column of an existing table, you use the ALTER TABLE<\/a><\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nALTER<\/span> COLUMN<\/span> column_name SET<\/span> DEFAULT<\/span> default_value;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example adds a DEFAULT<\/code> constraint to the quantity<\/code> column of the cart_itesm<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> cart_items\nALTER<\/span> COLUMN<\/span> quantity SET<\/span> DEFAULT<\/span> 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you describe the cart_items<\/code> table, you’ll see the changes:<\/p>\n\n\n
DESC cart_items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------+--------------+------+-----+---------+----------------+\n| Field     | Type         | Null | Key | Default | Extra          |\n+-----------+--------------+------+-----+---------+----------------+\n| item_id   | int          | NO   | PRI | NULL    | auto_increment |\n| name      | varchar(255) | NO   |     | NULL    |                |\n| quantity  | int          | NO   |     | 1       |                |\n| price     | decimal(5,2) | NO   |     | NULL    |                |\n| sales_tax | decimal(5,2) | NO   |     | 0.10    |                |\n+-----------+--------------+------+-----+---------+----------------+\n5 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement inserts a new row into the cart_items<\/code> table without specifying a value for the quantity<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> cart_items(name<\/span>, price, sales_tax)\nVALUES<\/span>('Maintenance services'<\/span>,25.99<\/span>, 0<\/span>)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The value of the quantity<\/code> column will default to 1:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> cart_items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+---------+----------------------+----------+-------+-----------+\n| item_id | name                 | quantity | price | sales_tax |\n+---------+----------------------+----------+-------+-----------+\n|       1 | Keyboard             |        1 | 50.00 |      0.10 |\n|       2 | Battery              |        4 |  0.25 |      0.10 |\n|       3 | Maintenance services |        1 | 25.99 |      0.00 |\n+---------+----------------------+----------+-------+-----------+\n3 rows in set (0.00 sec)    <\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Removing a DEFAULT constraint from a column<\/h2>\n\n\n\n
To remove a DEFAULT<\/code> constraint from a column, you use the ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name\nALTER<\/span> column_name DROP<\/span> DEFAULT<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example removes the DEFAULT<\/code> constraint from the quantity<\/code> column of the cart_items<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> cart_items\nALTER<\/span> COLUMN<\/span> quantity DROP<\/span> DEFAULT<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And here’s the new cart_items<\/code> structure:<\/p>\n\n\n
DESC cart_items;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------+--------------+------+-----+---------+----------------+\n| Field     | Type         | Null | Key | Default | Extra          |\n+-----------+--------------+------+-----+---------+----------------+\n| item_id   | int          | NO   | PRI | NULL    | auto_increment |\n| name      | varchar(255) | NO   |     | NULL    |                |\n| quantity  | int          | NO   |     | NULL    |                |\n| price     | decimal(5,2) | NO   |     | NULL    |                |\n| sales_tax | decimal(5,2) | NO   |     | 0.10    |                |\n+-----------+--------------+------+-----+---------+----------------+\n5 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
  • MySQL DEFAULT<\/code> constraints set default values for columns.<\/li>
  • Use DEFAULT default_value<\/code> to set a default constraint to a column.<\/li>
  • Use ALTER TABLE ... ALTER COLUMN ... SET DEFAULT<\/code> to add a DEFAULT<\/code> constraint to a column of an existing table.<\/li>
  • Use ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT<\/code> to drop a DEFAULT<\/code> constraint from a column of an existing table.<\/li><\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t