DEFAULT Constraint

What is a DEFAULT Constraint in MySQL?

A DEFAULT Constraint in MySQL is used to set a default value for a column when no value is provided during the insertion of a new record.

Syntax:

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value
);

Example:

Suppose the Student table contains three columns: Student_ID, Name, and City, with three rows.

The City field is set with a DEFAULT Constraint because if a user does not provide a city value while inserting a record, MySQL automatically inserts the default value.

A DEFAULT Constraint can be applied to multiple columns in a table, such as City and Status.

A DEFAULT Constraint does not prevent duplicate values. It only provides a default value when no value is specified.

It is also important to note that a DEFAULT Constraint does not create a unique identity for a record. It is used only to automatically assign values to columns.

Output:

+------------+---------+-----------+
| Student_ID | Name    | City      |
+------------+---------+-----------+
| 101        | Rahul   | Delhi     |
| 102        | Amit    | Delhi     |
| 103        | Neha    | Mumbai    |
+------------+---------+-----------+

Explanation:

The column City has a DEFAULT Constraint, and the default value is automatically inserted when no value is provided.

Because a DEFAULT Constraint is set on this column, MySQL uses the default value during record insertion if the user does not enter a value.

How Do You Set a DEFAULT Constraint on a Table in MySQL?

You can set a DEFAULT Constraint on a table by using the following two methods:

1. While creating a new table using the CREATE TABLE statement.

   
   

   Example:

   CREATE TABLE Student (
       Student_ID INT,
       Name VARCHAR(50),
       City VARCHAR(50) DEFAULT 'Delhi'
   );
   

   
   

   Steps:

   1. First, you write the CREATE TABLE statement, and then you write the table name.
   2. Open the parentheses (.
   3. Inside the parentheses, write the column names and their data types.
   4. For the column where you want to set a default value, write the DEFAULT keyword after the data type of that column.
   5. Write the default value after the DEFAULT keyword.
   6. Write the remaining column names with their data types, separating each field with a comma.
   7. Close the parentheses ) and end the query with a semicolon ;.
   8. Finally, execute the query to create the table with the DEFAULT Constraint.

2. By adding a DEFAULT Constraint to an existing table using the ALTER TABLE statement.

   
   

   Example:

   ALTER TABLE Student
   ALTER City SET DEFAULT 'Delhi';
   

   
   

   Steps:

   1. First, write the ALTER TABLE statement.
   2. Next, write the name of the existing table.
   3. Write the ALTER clause with the column name.
   4. Write the SET DEFAULT keyword with the value that you want to set as the default value.
   5. Close the query and end it with a semicolon ;.
   6. Finally, execute the query to add the DEFAULT Constraint to the existing table.

Advantages of a DEFAULT Constraint

• Provides Automatic Values

  A DEFAULT Constraint automatically inserts a predefined value when a user does not provide a value for a column.

• Reduces Data Entry Work

  A DEFAULT Constraint reduces the need to enter the same value repeatedly while inserting records.

• Maintains Data Consistency

  A DEFAULT Constraint helps maintain consistent data by providing the same default value for missing entries.

• Improves Data Management

  A DEFAULT Constraint makes database management easier by automatically assigning values when required.

Common Mistakes When Setting a DEFAULT Constraint in MySQL

1. To Set an Incorrect Default Value

   You should not set an incorrect default value because it may store unwanted data in the column.

2. To Apply DEFAULT Constraint on the Wrong Column

   You should choose the correct column for a DEFAULT Constraint because the default value should match the purpose of that column.

3. To Forget the Data Type Compatibility

   You should use a default value that matches the column data type; otherwise, MySQL may show an error.

4. To Confuse DEFAULT Constraint with NOT NULL Constraint

   You should not confuse a DEFAULT Constraint with a NOT NULL Constraint because DEFAULT provides a value, while NOT NULL prevents NULL values.

5. Choose the Incorrect Data Type

   You should choose the correct data type for the DEFAULT column because the default value should be stored properly.

6. To Create Unnecessary DEFAULT Values

   You should not create unnecessary DEFAULT values because it can affect proper database design.