MySQL

In MySQL, the NULL and NOT NULL constraints are used to define whether a column can accept NULL values. ANULLvalue represents the absence of data, whileNOT NULL ensures that a column must always contain a value. Understanding these constraints is crucial for designing robust databases and ensuring data integrity. Below is a guide on using NULL and NOT NULL constraints with code samples for tables related to a company database, including company, employees, departments, andbranches.

Understanding NULL vs NOT NULL

1. NULL
   • A column with the NULLconstraint can store missing or unknown data.
   • Useful when a value might not be applicable or is not yet known.
2. NOT NULL
   • A column with theNOT NULL constraint must always have a value.
   • Ensures that the column cannot have missing or undefined data.

Code Samples

1. Creating the companyTable with NULL and NOT NULL Constraints

   CREATE TABLE company (
   company_id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   founded DATE NULL
   );
           

   • company_id: Unique identifier for each company (auto-incremented).
   • name: Name of the company (must have a value; cannot be NULL). .
   • founded: Date when the company was founded (can be NULLif the founding date is unknown).
2. Creating the employeesTable with NULL and NOT NULL Constraints

CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
hire_date DATE NOT NULL,
department_id INT NULL
);

        

• employee_id: Unique identifier for each employee (auto-incremented).
• first_name and last_name: Employee's names (must have values; cannot be NULL).
• email: Email address (must have a value; cannot be NULL and must be unique).
• hire_date: Date the employee was hired (must have a value; cannot be NULL).
• department_id: Reference to the department (can be NULL if the employee is not assigned to a department).
3. Creating the departments Table with NULL and NOT NULL Constraints

CREATE TABLE departments (
deartment_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
location VARCHAR(100) NULL
);
        

• department_id: Unique identifier for each department (auto-incremented).
• name: Name of the department (must have a value; cannot beNULL).
• location: Location of the department (can be NULL if the location is not specified).
4. Creating the branches Table with NULL and NOT NULL Constraints

   CREATE TABLE branches (
   branch_id INT AUTO_INCREMENT PRIMARY KEY,
   address VARCHAR(255) NOT NULL,
   city VARCHAR(100) NOT NULL,
   state VARCHAR(100) NULL,
   postal_code VARCHAR(20) NULL
   );
           

   • branch_id: Unique identifier for each branch (auto-incremented).
   • address and city: Required fields; cannot be NULL.
   • state and postal_code: Optional fields; can be NULLif not provided.

Important Considerations

• Data Integrity: Using NOT NULL ensures that important columns always have data, which helps maintain data integrity.
• Default Values: If a column is NOT NULL, consider providing a default value to avoid errors during data insertion.
• Design Choices: Choose NULL or NOT NULL based on whether it makes sense for a column to have missing data.

By understanding and applying NULL and NOT NULLconstraints as shown in these examples, beginners can create well-structured tables that enforce data integrity and meet application requirements.

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