When creating tables in MySQL, choosing the correct data type determines what kind of information each column can store. Constraints define rules for that data, helping maintain consistency, accuracy, and relationships between tables.
Learning Objectives
Understand the purpose of MySQL data types
Work with numeric data types
Store dates and times
Work with character and text data
Understand binary data types
Use ENUM and BLOB
Create primary and foreign keys
Apply UNIQUE, NOT NULL, DEFAULT, and CHECK constraints
Understand how foreign key checks affect related tables
What are MySQL Data Types?
A data type defines the type of value that can be stored in a column.
For example, a student table might contain:
CREATE TABLE Students (
student_id INT,
student_name VARCHAR(100),
marks DECIMAL(5,2),
joining_date DATE
);
Here:
INT stores whole numbers.
VARCHAR stores variable-length text.
DECIMAL stores exact decimal values.
DATE stores a calendar date.
Selecting appropriate data types helps structure information correctly and use database storage effectively.
Numeric Data Types
INT
INT stores whole numbers without decimal values.
Example:
age INT
Typical values include:
18
25
100
It is commonly used for IDs, quantities, counts, and other whole-number values.
DECIMAL
DECIMAL stores exact numeric values with a defined number of digits and decimal places.
Example:
salary DECIMAL(10,2)
A value could be:
12500.50
DECIMAL is useful for values such as prices, salaries, and financial amounts where exact decimal representation is important.
BIT
BIT stores bit values.
Example:
status BIT(1)
It can be used when a column needs to represent binary information.
BOOLEAN
BOOLEAN can be used for true/false-style values.
Example:
is_active BOOLEAN
A simple table could contain:
CREATE TABLE Users (
user_id INT,
user_name VARCHAR(100),
is_active BOOLEAN
);
Date and Time Data Types
MySQL provides several data types for storing dates and times.
DATE
DATE stores a calendar date.
Format:
YYYY-MM-DD
Example:
2026-07-31
Column definition:
joining_date DATE
TIME
TIME stores a time value.
Example:
10:30:00
Column definition:
start_time TIME
DATETIME
DATETIME stores both a date and time.
Example:
2026-07-31 10:30:00
Column definition:
created_at DATETIME
TIMESTAMP
TIMESTAMP can also store date and time information and is commonly used for tracking when records are created or modified.
Example:
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Character and Text Data Types
CHAR
CHAR stores fixed-length character values.
Example:
country_code CHAR(2)
This is suitable when values generally have a fixed length, such as:
QA
IN
US
VARCHAR
VARCHAR stores variable-length character data.
Example:
student_name VARCHAR(100)
It is commonly used for:
Names
Email addresses
Titles
Departments
Product names
TEXT
TEXT is used for longer text content.
Example:
description TEXT
It can be useful for descriptions, comments, notes, and other longer textual values.
Binary Data Types
BINARY
BINARY stores fixed-length binary data.
Example:
binary_code BINARY(16)
VARBINARY
VARBINARY stores variable-length binary data.
Example:
binary_data VARBINARY(255)
Unlike CHAR and VARCHAR, these types store binary byte strings rather than character strings.
ENUM
ENUM restricts a column to one value from a predefined list.
Example:
status ENUM('Active', 'Inactive', 'Pending')
You can use it when the valid values for a field are known in advance.
For example:
CREATE TABLE Accounts (
account_id INT,
account_name VARCHAR(100),
status ENUM('Active', 'Inactive', 'Pending')
);
BLOB
BLOB stands for Binary Large Object and is used to store binary data.
Example:
document BLOB
BLOB fields can be used when binary information needs to be stored within a database.
MySQL Constraints
Constraints are rules applied to columns or tables to control what data can be stored.
The program covers:
Primary Key
Foreign Key
UNIQUE
NOT NULL
DEFAULT
CHECK
PRIMARY KEY Constraint
A primary key uniquely identifies each record in a table.
Example:
CREATE TABLE Students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100)
);
Values in student_id must uniquely identify each student.
A common approach is to combine a primary key with AUTO_INCREMENT:
student_id INT AUTO_INCREMENT PRIMARY KEY
FOREIGN KEY Constraint
A foreign key establishes a relationship between tables.
Consider a Departments table:
CREATE TABLE Departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
An Employees table can reference it:
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id)
REFERENCES Departments(department_id)
);
Here:
Departments.department_id
↓
Employees.department_id
This creates a relationship between employees and their departments.
UNIQUE Constraint
UNIQUE prevents duplicate values from being stored in a column covered by the constraint.
Example:
email VARCHAR(100) UNIQUE
A table could be created as:
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(100) UNIQUE
);
This prevents two records from using the same non-NULL email value.
NOT NULL Constraint
NOT NULL requires a column to contain a value.
Example:
student_name VARCHAR(100) NOT NULL
For example:
CREATE TABLE Students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);
MySQL will not allow student_name to be stored as NULL.
DEFAULT Constraint
DEFAULT provides a predefined value when a value isn't explicitly supplied during insertion.
Example:
status VARCHAR(20) DEFAULT 'Active'
For example:
CREATE TABLE Users (
user_id INT PRIMARY KEY,
user_name VARCHAR(100),
status VARCHAR(20) DEFAULT 'Active'
);
If a new record is inserted without specifying status, MySQL can use:
Active
CHECK Constraint
CHECK defines a condition that stored values must satisfy.
Example:
age INT CHECK (age >= 18)
A table could contain:
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100),
age INT CHECK (age >= 18)
);
Values that violate the enforced condition are rejected.
Using Multiple Constraints
Multiple constraints can be applied within the same table.
Example:
CREATE TABLE Employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10,2) CHECK (salary >= 0),
status VARCHAR(20) DEFAULT 'Active'
);
This table demonstrates several rules:
employee_id uniquely identifies records.
employee_name is required.
email must be unique when provided.
salary cannot be negative.
status uses Active as its default value.
Disabling Foreign Key Checks
MySQL allows foreign key checking to be temporarily disabled.
To disable it:
SET FOREIGN_KEY_CHECKS = 0;
To enable it again:
SET FOREIGN_KEY_CHECKS = 1;
This can be useful during certain database maintenance or data-loading operations.
Important: Foreign key checks help protect referential integrity. Disable them only when necessary and enable them again after completing the required operation.
Data Types vs Constraints
Data Types | Constraints |
Define what kind of data a column stores | Define rules for stored data |
Examples: INT, VARCHAR, DATE | Examples: PRIMARY KEY, NOT NULL |
Control the representation of values | Help enforce data integrity |
Selected when defining columns | Applied to columns or tables |
Both are essential when designing reliable database tables.
Practical Example
Create a simple employee database:
CREATE DATABASE EmployeeDB;
USE EmployeeDB;
CREATE TABLE Employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10,2) CHECK (salary >= 0),
joining_date DATE,
status ENUM('Active', 'Inactive') DEFAULT 'Active'
);
Then run:
DESCRIBE Employees;
This example combines database creation, data types, AUTO_INCREMENT, and several constraints in one table.
Common Mistakes
When working with data types and constraints, avoid:
Using numeric types for information that should be treated as text
Selecting a VARCHAR length that doesn't fit the intended data
Forgetting to define a primary key
Creating foreign keys that reference incompatible columns
Inserting NULL into a NOT NULL column
Attempting to insert duplicate values into a UNIQUE field
Providing values that violate a CHECK condition
Disabling foreign key checks and forgetting to enable them again
Still need help?
Contact us