MySQL DEFAULT Constraint: Definition, Syntax, and Examples

The DEFAULT constraint in MySQL is used to assign a default value to a column when no value is provided during an INSERT operation. This ensures that fields have a valid value even if the user doesn't explicitly enter one.

Key Features of DEFAULT Constraint

Automatically assigns a specified value when no input is provided.
Can be applied to CHAR, VARCHAR, INT, DATE, FLOAT, and other data types.
Helps maintain data consistency and avoid NULL values.
Cannot be used with TEXT, BLOB, or JSON data types.

Syntax for DEFAULT Constraint

CREATE TABLE table_name (
  column1 data_type DEFAULT default_value,
  column2 data_type
);

DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:

Example

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(50) NOT NULL,

age INT DEFAULT 18,

city VARCHAR(50) DEFAULT 'Unknown',

admission_date DATE DEFAULT CURRENT_DATE

);

Explanation:

age → Default value is 18 if no age is provided.
city → Default is 'Unknown' if no city is specified.
admission_date → Uses CURRENT_DATE to store the current date if not specified.

DEFAULT on ALTER TABLE

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

Example

ALTER TABLE students

ALTER COLUMN age SET DEFAULT 20;

Explanation:

The default value for the age column will now be 20 instead of 18.

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

Example

ALTER TABLE students

ALTER COLUMN city DROP DEFAULT;

Explanation:

The city column will no longer have a default value.
Inserting without specifying a city will result in a NULL value.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.