MySQL DEFAULT Constraint

Last Updated: 01 July, 2023

In MySQL, DEFAULT constraints are used to specify default values for columns in a table. A default value is automatically assigned to a column if no explicit value is provided during an insert operation.

Here's an example of creating a table with a DEFAULT constraint in MySQL:

DEFAULT value:

When a new row is inserted into the table and no explicit value is provided for the column, the default value specified by the constraint will be used.

 CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT DEFAULT 18
 );

DEFAULT CURRENT_TIMESTAMP:

This constraint sets the current timestamp as the default value for a column. It is commonly used with timestamp or datetime columns to automatically set the current timestamp when a row is inserted.

 CREATE TABLE posts (
  id INT PRIMARY KEY,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

DEFAULT NULL:

By default, if a column does not have a default constraint defined, its default value is NULL. However, we can explicitly specify DEFAULT NULL to make it more explicit.

 CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) DEFAULT NULL
 );

Add or Modify DEFAULT Constraint:

It's worth noting that default constraints can also be added or modified after the table creation using the ALTER TABLE statement.

 ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

Additionally, we can remove a default constraint from a column using the ALTER TABLE statement as well.

 ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

Removing default values:

To remove a DEFAULT constraint from a column, we can use the ALTER TABLE statement with the ALTER TABLE keyword and set the default value to NULL.

 ALTER TABLE mytable MODIFY name VARCHAR(50) DEFAULT NULL;

Remember that DEFAULT constraints are applied when a new row is inserted into a table and no explicit value is provided for the column. If a value is explicitly provided during an insert operation, it will override the default value.