NOT NULL and DEFAULT constraints in MySQL - Mini Project: Build & Apply

Using NOT NULL and DEFAULT Constraints in MySQL

📖 Scenario: You are creating a simple database table to store information about employees in a company. Each employee must have a name and a department. The department should default to 'General' if not specified.

🎯 Goal: Create a MySQL table called employees with columns that use NOT NULL and DEFAULT constraints to ensure data integrity.

📋 What You'll Learn

Create a table named employees

Add a column id as an integer primary key

Add a column name as a VARCHAR(50) that cannot be NULL

Add a column department as a VARCHAR(50) that cannot be NULL and defaults to 'General'

💡 Why This Matters

🌍 Real World

Ensuring data integrity in employee databases by preventing missing important information and providing default values.

💼 Career

Database administrators and developers often use <code>NOT NULL</code> and <code>DEFAULT</code> constraints to maintain clean and reliable data.

1

Create the employees table with id and name columns

Write a MySQL statement to create a table called employees with two columns: id as an integer primary key, and name as a VARCHAR(50) that cannot be NULL using the NOT NULL constraint.

MySQL

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
-- Your code here

Need a hint?

Use NOT NULL after the name column type to prevent NULL values.

2

Add the department column with NOT NULL and DEFAULT constraints

Add a column called department to the employees table with type VARCHAR(50). This column must use NOT NULL and have a default value of 'General' using the DEFAULT constraint.

MySQL

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL DEFAULT 'General'
);
-- Your code here

Need a hint?

Use DEFAULT 'General' after NOT NULL to set the default value.

3

Insert a new employee without specifying the department

Write a MySQL INSERT statement to add a new employee with id 1 and name 'Alice' into the employees table without specifying the department column.

MySQL

INSERT INTO employees (id, name) VALUES (1, 'Alice');
-- Your code here

Need a hint?

Omit the department column in the INSERT statement to use the default value.

4

Insert a new employee specifying the department

Write a MySQL INSERT statement to add a new employee with id 2, name 'Bob', and department 'Sales' into the employees table.

MySQL

INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'Sales');
-- Your code here

Need a hint?

Include the department column and value in the INSERT statement.