• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Create Table

← Previous Next →

The MySQL CREATE TABLE statement is used to define and create a new table in a database. Tables are the core building blocks of a MySQL database because they store all structured data in rows and columns.

Creating tables properly is essential for data integrity, efficient querying, and long-term maintenance. This tutorial explains the syntax, examples, best practices, and practical use cases for creating tables in MySQL.

What Is CREATE TABLE?

In MySQL, a table is a collection of rows (records) and columns (fields). The CREATE TABLE statement allows you to:

  • Define the table structure with column names and data types.

  • Specify constraints such as primary keys, unique keys, or foreign keys.

  • Set default values and allow or disallow NULL values.

Key Points:

  • Every table belongs to a database.

  • Tables are logical structures for storing related data.

  • Proper column data types optimize storage and performance.

  • Constraints ensure data accuracy and prevent invalid entries.

CREATE TABLE Syntax

The basic syntax is:

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Explanation:

  • table_name: The name of the new table.

  • column1, column2: Names of columns in the table.

  • datatype: Defines the type of data stored, such as INT, VARCHAR, or DATE.

  • [constraints]: Optional rules, like PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, or AUTO_INCREMENT.

Data Types Overview

Common MySQL data types include:

Data Type Description
INT Integer numbers
VARCHAR(n) Variable-length string up to n characters
CHAR(n) Fixed-length string
DATE Date values (YYYY-MM-DD)
DATETIME Date and time values
FLOAT, DOUBLE Decimal numbers
TEXT Large text content
BOOLEAN True/False values

Choosing the correct data type ensures data integrity and optimized storage.

Basic Example

Creating a simple table students:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    city VARCHAR(50),
    age INT,
    enrollment_date DATE
);

Explanation:

  • student_id is the primary key and auto-increments for each new record.

  • student_name cannot be NULL.

  • city, age, and enrollment_date are optional.

  • Each column has an appropriate data type.

Creating a Table with Constraints

Constraints enforce rules on the table’s data:

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_code VARCHAR(10) UNIQUE,
    credits INT DEFAULT 3
);

Explanation:

  • course_code must be unique.

  • credits defaults to 3 if no value is provided.

  • Constraints help maintain data consistency and prevent duplicate or invalid entries.

Creating a Table with Foreign Key

Foreign keys enforce referential integrity between tables:

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Explanation:

  • student_id and course_id reference primary keys in other tables.

  • Ensures that every enrollment corresponds to an existing student and course.

  • Prevents insertion of invalid or orphaned records.

Table Options

You can define additional table options:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Explanation:

  • ENGINE=InnoDB supports transactions and foreign keys.

  • CHARSET=utf8mb4 supports multilingual data.

  • created_at automatically stores the insertion timestamp.

Checking Tables

To see the structure of a table:

DESCRIBE students;

To list all tables in a database:

SHOW TABLES;

Best Practices for Creating Tables

  1. Meaningful Names: Use descriptive table names like students, orders, or employees.

  2. Lowercase with Underscores: Avoid spaces and special characters.

  3. Choose Appropriate Data Types: Optimizes storage and improves query performance.

  4. Use Constraints: Primary keys, foreign keys, unique constraints, and defaults ensure data integrity.

  5. Set Character Set: Use utf8mb4 for modern applications.

  6. Plan for Scalability: Consider indexing frequently queried columns and table growth.

Common Mistakes to Avoid

  1. Skipping Constraints: Can lead to duplicate or invalid data.

  2. Using Wrong Data Types: May waste storage or cause data truncation.

  3. Ignoring Foreign Keys: Leads to orphaned or inconsistent data in related tables.

  4. Complex or Long Names: Makes queries cumbersome and error-prone.

  5. Not Considering Null Values: Decide which columns can or cannot be NULL.

Practical Use Cases

  1. School Database: students, courses, and enrollments tables.

  2. E-commerce: products, orders, customers, and order_items tables.

  3. HR Management: employees, departments, and salaries tables.

  4. Blog Application: posts, authors, and comments tables.

  5. Inventory System: items, suppliers, and stock_logs tables.

Summary of the Tutorial

The MySQL CREATE TABLE statement is a fundamental skill for any MySQL developer.

  • Define tables with columns, data types, and constraints.

  • Use primary and foreign keys for data integrity.

  • Include default values and proper character sets.

  • Follow best practices for naming, constraints, and scalability.

Creating tables thoughtfully ensures efficient data management, easier maintenance, and reliable database applications.

Practice Questions

  1. Write a query to create a table students with columns student_id (INT, primary key, auto-increment), student_name (VARCHAR), age (INT), and city (VARCHAR).

  2. Create a table courses with course_id (INT, primary key), course_name (VARCHAR, not null), credits (INT, default 3), and course_code (VARCHAR, unique).

  3. Write a query to create a table enrollments with enrollment_id (INT, primary key), student_id (INT), course_id (INT), and foreign keys referencing students and courses.

  4. Create a table products with product_id (INT, primary key, auto-increment), product_name (VARCHAR), price (DECIMAL), and created_at (DATETIME, default current timestamp).

  5. Write a query to create a table employees with employee_id (INT, primary key), employee_name (VARCHAR), department (VARCHAR), and hire_date (DATE).

  6. Create a table orders with order_id (INT, primary key), customer_id (INT), order_date (DATETIME), and status (VARCHAR).

  7. Write a query to create a table comments with comment_id (INT, primary key, auto-increment), post_id (INT), comment_text (TEXT), and created_at (DATETIME, default current timestamp).

  8. Create a table suppliers with supplier_id (INT, primary key), supplier_name (VARCHAR), contact_email (VARCHAR), and phone (VARCHAR).

  9. Write a query to create a table inventory with item_id (INT, primary key), item_name (VARCHAR), quantity (INT), and last_updated (DATETIME).

  10. Create a table departments with department_id (INT, primary key), department_name (VARCHAR, not null), and manager_id (INT).

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top