WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL DEFAULT

Previous Next

MySQL DEFAULT Constraint

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