MySQL

The DEFAULT constraint in MySQL is used to provide a default value for a column when no value is specified during the insertion of a record. This ensures that a column always has a value, even if one is not explicitly provided. Default values are particularly useful for setting common values or for ensuring that columns have a meaningful value in the absence of explicit input. Below is a guide on using the DEFAULT constraint in MySQL, with code samples for tables related to a company database, including company, employees, departments, and branches

Understanding the DEFAULT Constraint

1. Purpose
   • The DEFAULT constraint automatically assigns a predefined value to a column if no value is provided during record insertion.
   • Useful for simplifying data entry and ensuring columns have consistent values.
2. Syntax
   • The DEFAULT constraint is defined in the CREATE TABLE or ALTER TABLE statement.
   • The syntax is COLUMN_NAME DATA_TYPE DEFAULT default_value.

Code Samples

1. Creating the company Table with a DEFAULT Constraint

   CREATE TABLE company (
     company_id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     established YEAR DEFAULT YEAR(CURDATE()),
     revenue DECIMAL(15, 2) DEFAULT 0.00
   );

   • company_id: Unique identifier for each company (auto-incremented).
   • name: Name of the company (cannot be NULL).
   • established: Year when the company was established (defaults to the current year if not specified).
   • revenue: Annual revenue (defaults to 0.00 if not specified).
2. Creating the employees Table with DEFAULT Constraints

   CREATE TABLE employees (
     employee_id INT AUTO_INCREMENT PRIMARY KEY,
     first_name VARCHAR(50) NOT NULL,
     last_name VARCHAR(50) NOT NULL,
     salary DECIMAL(10, 2) DEFAULT 30000.00,
     hire_date DATE DEFAULT CURDATE(),
     department_id INT DEFAULT NULL,
     status VARCHAR(10) DEFAULT 'Active'
   );

   • employee_id: Unique identifier for each employee (auto-incremented).
   • first_nameand last_name: Required fields (cannot be NULL).
   • salary: Salary of the employee (defaults to 30,000.00 if not specified).
   • hire_date: Date the employee was hired (defaults to the current date if not specified).
   • department_id: Reference to the department (defaults to NULL if not specified).
   • status: Employment status (defaults to 'Active').
3. Creating the departments Table with a DEFAULT Constraint

   CREATE TABLE departments (
     department_id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL,
     budget DECIMAL(12, 2) DEFAULT 100000.00,
     num_employees INT DEFAULT 0
   );

   • department_id: Unique identifier for each department (auto-incremented).
   • name: Name of the department (cannot be NULL).
   • budget: Budget allocated to the department (defaults to 100,000.00 if not specified).
   • num_employees: Number of employees in the department (defaults to 0 if not specified).
4. Creating the branches Table with a DEFAULT Constraint

   CREATE TABLE branches (
     branch_id INT AUTO_INCREMENT PRIMARY KEY,
     address VARCHAR(255) NOT NULL,
     city VARCHAR(100) NOT NULL,
     state VARCHAR(100) NOT NULL,
     postal_code VARCHAR(20) DEFAULT '00000',
     opening_hours VARCHAR(50) DEFAULT '9am-5pm'
   );

   • branch_id: Unique identifier for each branch (auto-incremented).
   • address,city, and state: Required fields (cannot be NULL).
   • postal_code: Postal code (defaults to '00000' if not specified).
   • opening_hours: Description of the branch's opening hours(defaults to '9am-5pm')..

Important Considerations

• Default Value Types: Default values can be constants or expressions (e.g., CURDATE() for the current date).
• Applicability: Default values are applied only when a column is not explicitly assigned a value during insertion.
• Data Integrity: Ensure default values are appropriate and meaningful for your application to maintain data integrity.

By using the DEFAULT constraint as shown in these examples, beginners can simplify data entry and ensure that their tables are populated with sensible default values in MySQL.

To gain complete access, login with gmail or outlook, no need of signup, click here