Creating Tables Using CREATE TABLE in MySQL – Complete Notes

Creating tables is one of the most important tasks in MySQL database management. The CREATE TABLE statement is used to define a table's structure, including its columns, data types, constraints, and relationships. These notes explain how to create tables in MySQL with syntax, examples, practical exercises, common errors, and best practices.

Course: JustAcademy MySQL Training

Course Demo: Register for MySQL Course Demo

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand the purpose of the CREATE TABLE statement.
• Write the correct syntax for creating MySQL tables.
• Select appropriate data types for columns.
• Define primary keys, foreign keys, and other constraints.
• Create tables with required and optional columns.
• Use IF NOT EXISTS to avoid errors when a table already exists.
• Verify table structures using MySQL commands.
• Identify and troubleshoot common table-creation errors.

2. What Is a Table in MySQL?

A table is a database object that stores related information in rows and columns. Each column represents a particular attribute, while each row represents one record.

For example, a students table may contain student IDs, names, email addresses, and course names.

Important terms:

• Table: A structure that stores related records.
• Row: A single record in a table.
• Column: A field that describes a particular attribute.
• Data type: Defines the kind of value a column can store.
• Constraint: A rule that controls which values can be stored.

3. What Is the CREATE TABLE Statement?

The CREATE TABLE statement is a Data Definition Language (DDL) command used to create a new table inside a database. It specifies the table name, column names, data types, and optional constraints.

Example:

CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(100),
    email VARCHAR(150),
    age INT
);

This command creates a table named students with four columns. It defines the structure but does not insert any student records.

4. Basic Syntax of CREATE TABLE

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    column3 data_type
);

Syntax explanation:

• CREATE TABLE tells MySQL to create a table.
• table_name is the name assigned to the table.
• column1, column2, and other column names define the fields.
• data_type specifies the kind of data each column can store.
• Commas separate column definitions and constraints.
• The semicolon ends the SQL statement.

The table definition is enclosed in parentheses. Each column must have a valid name and data type.

5. Selecting a Database Before Creating a Table

Before creating a table, select the database in which the table should be stored. You can use the USE statement.

CREATE DATABASE IF NOT EXISTS college_db;
USE college_db;

CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(100),
    course VARCHAR(100)
);

Explanation:

1. CREATE DATABASE IF NOT EXISTS creates the database only if it does not already exist.
2. USE college_db selects the database for subsequent statements.
3. CREATE TABLE students creates the table inside the selected database.

Note: If you do not select a database or qualify the table name with a database name, MySQL may return an error indicating that no database is selected.

6. Common MySQL Data Types Used in Tables

Choosing suitable data types improves data accuracy and helps manage storage efficiently.

Example:

CREATE TABLE courses (
    course_id INT,
    course_name VARCHAR(100),
    course_fee DECIMAL(10,2),
    start_date DATE,
    is_active BOOLEAN
);

Use DECIMAL for values requiring exact decimal precision, such as course fees. Choose data types based on the actual range and meaning of the information.

7. Creating a Table with a Primary Key

A primary key uniquely identifies every record in a table. It must contain unique values and cannot contain NULL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    email VARCHAR(150)
);

In this example, student_id is the primary key. Two records cannot have the same student ID.

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute automatically generates a new numeric value when a record is inserted without a value for that column.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

MySQL generates the next available auto-increment value for each new row when the column is omitted or assigned NULL. This is useful for automatically generated identifiers.

8. Using NOT NULL and DEFAULT Constraints

Constraints help enforce rules on the values stored in a table.

NOT NULL

The NOT NULL constraint prevents a column from storing a NULL value.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(80)
);

Here, every employee record must include a non-NULL value for employee_name.

DEFAULT

The DEFAULT constraint specifies a value MySQL uses when an insert statement omits that column.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(80) DEFAULT 'General',
    status VARCHAR(20) DEFAULT 'Active'
);

If an insert statement omits department, MySQL uses General. An explicitly supplied NULL is not automatically replaced by the default.

9. Using UNIQUE Constraints

The UNIQUE constraint prevents duplicate values in a column or combination of columns. It is useful for email addresses, usernames, and other fields that should be unique.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE
);

This table prevents duplicate non-NULL usernames and email addresses. MySQL permits multiple NULL values in a typical unique index, so use NOT NULL as well when a value is mandatory.

10. Using CHECK Constraints

A CHECK constraint restricts values based on a logical condition. MySQL 8.0.16 and later enforces CHECK constraints.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    CHECK (price >= 0),
    CHECK (stock >= 0)
);

The table requires prices and stock quantities to be non-negative. When a row violates an enforced check constraint, MySQL rejects the operation.

11. Creating Tables with Foreign Keys

A foreign key establishes a relationship between tables. It helps ensure that a value in the child table refers to an existing row in the parent table.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_employees_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Explanation:

• departments is the parent table.
• employees is the child table.
• department_id in employees references the primary key in departments.
• A non-NULL department ID in an employee row must match an existing department.
• The referenced and referencing columns must have compatible definitions, and the tables must use a storage engine that supports foreign keys, such as InnoDB.

Important: Create the parent table before the child table. Do not add cascading actions such as ON DELETE CASCADE unless they match the intended data-management rules.

12. Creating a Table with Multiple Constraints

You can define several constraints in one table, including a primary key, unique columns, default values, and checks.

CREATE TABLE course_enrollments (
    enrollment_id INT AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Enrolled',
    PRIMARY KEY (enrollment_id),
    UNIQUE (student_id, course_id),
    CHECK (status IN ('Enrolled', 'Completed', 'Cancelled'))
);

The composite UNIQUE constraint prevents the same student from being enrolled in the same course more than once. A student can still enroll in different courses.

13. Creating a Table Only If It Does Not Exist

Use IF NOT EXISTS when you want MySQL to avoid the error that would otherwise occur if a table with the same name already exists.

CREATE TABLE IF NOT EXISTS students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

If the table already exists, MySQL produces a warning rather than creating another table. It does not compare or update the existing table's structure to match the definition in your statement.

14. Creating a Table with a Database-Qualified Name

You can specify the database name directly instead of selecting the database with USE.

CREATE TABLE college_db.teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_name VARCHAR(100) NOT NULL,
    subject_name VARCHAR(100)
);

This creates the teachers table in college_db, provided the database exists and you have the necessary privileges.

15. Creating Tables Using MySQL Workbench

MySQL Workbench provides a graphical interface for writing and executing SQL statements.

1. Open MySQL Workbench.
2. Connect to your MySQL server.
3. Select the required database in the Schemas panel or run a USE statement.
4. Open a new SQL query tab.
5. Type the CREATE TABLE statement.
6. Click the Execute button or use the keyboard shortcut shown in Workbench.
7. Refresh the Schemas panel to view the new table.
8. Expand the table's columns to inspect its structure.

Example query:

USE college_db;

CREATE TABLE teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    joining_date DATE
);

16. How to Verify a Newly Created Table

After creating a table, verify that it exists and that its structure is correct.

Show Tables

SHOW TABLES;

Displays tables in the currently selected database.

Describe a Table

DESCRIBE students;

Displays column names, data types, nullability, key information, default values, and extra attributes.

Use SHOW CREATE TABLE

SHOW CREATE TABLE students;

Displays the SQL definition of the table, including its columns, constraints, indexes, and table options.

Check the Current Database

SELECT DATABASE();

Returns the currently selected database, or NULL if no database is selected.

17. Complete Practical Example: Student Management Database

Follow these steps to create a simple student management database and its tables.

Step 1: Create and Select the Database

CREATE DATABASE IF NOT EXISTS student_management;
USE student_management;

Step 2: Create the Courses Table

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL UNIQUE,
    course_fee DECIMAL(10,2) NOT NULL,
    CHECK (course_fee >= 0)
);

Step 3: Create the Students Table

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    date_of_birth DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Step 4: Create the Enrollments Table

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Enrolled',
    CONSTRAINT fk_enrollments_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    CONSTRAINT fk_enrollments_course
        FOREIGN KEY (course_id)
        REFERENCES courses(course_id),
    UNIQUE (student_id, course_id),
    CHECK (status IN ('Enrolled', 'Completed', 'Cancelled'))
);

Step 5: Verify the Tables

SHOW TABLES;
DESCRIBE courses;
DESCRIBE students;
DESCRIBE enrollments;
SHOW CREATE TABLE enrollments;

Result: The database contains three related tables. The enrollments table connects students to courses through foreign keys and prevents duplicate student-course pairs.

18. Difference Between CREATE TABLE and INSERT INTO

Example of inserting a record after creating the table:

INSERT INTO students (student_name, email)
VALUES ('Rahul Sharma', '[email protected]');

This statement adds a row to the previously created students table. It does not create the table itself.

19. Common Errors and Troubleshooting

Example of a Syntax Error

Incorrect statement:

CREATE TABLE students (
    student_id INT
    student_name VARCHAR(100)
);

The comma between the column definitions is missing.

Correct statement:

CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(100)
);

20. Best Practices for Creating Tables

• Use clear, meaningful, and consistent table and column names.
• Choose data types based on the data being stored and its expected range.
• Define a primary key for tables that require reliable record identification.
• Use NOT NULL for mandatory fields.
• Use UNIQUE for fields or combinations that must not contain duplicate values.
• Use foreign keys when relationships between tables need database-level integrity enforcement.
• Use DECIMAL for exact monetary values instead of approximate floating-point types.
• Use DEFAULT values only when they represent valid business rules.
• Keep column definitions readable and use consistent indentation.
• Avoid reserved SQL keywords as identifiers unless they are quoted appropriately.
• Inspect existing tables before attempting to recreate or change them.
• Use SHOW CREATE TABLE to review the final table definition.
• Plan indexes around query patterns rather than adding unnecessary indexes.
• Never store passwords in plain text; store securely generated password hashes using appropriate application-side password-hashing methods.
• Use the least-privilege principle when assigning database permissions.

21. Practice Exercises

Try creating the following tables independently.

1. Create a table named employees with employee ID, name, department, salary, and joining date.
2. Create a table named products with product ID, product name, price, and stock quantity.
3. Create a table named departments with department ID and department name. Make department names unique.
4. Create an orders table with an auto-increment primary key and a default order status.
5. Create a bookings table with a foreign key referencing a customer table.
6. Create a table with IF NOT EXISTS and verify the result using SHOW TABLES.
7. Use DESCRIBE and SHOW CREATE TABLE to inspect a table's structure.

Sample Solution

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    CHECK (price >= 0),
    CHECK (stock_quantity >= 0)
);

22. Interview Questions and Answers

Q1. What is CREATE TABLE in MySQL?

Answer: It is a DDL statement used to create a new table by defining its columns, data types, and constraints.

Q2. What is the basic syntax of CREATE TABLE?

Answer: CREATE TABLE table_name (column_name data_type, ...);

Q3. What is the purpose of a primary key?

Answer: It uniquely identifies each record in a table and does not permit NULL values.

Q4. What does AUTO_INCREMENT do?

Answer: It generates the next automatic numeric value for a column when a new row is inserted without an explicit value for that column.

Q5. What is the difference between NOT NULL and UNIQUE?

Answer: NOT NULL prevents a column from containing NULL; UNIQUE prevents duplicate non-NULL values or duplicate combinations, depending on the constraint.

Q6. What does IF NOT EXISTS do?

Answer: It avoids a table-exists error when a table with the specified name is already present. It does not modify that existing table.

Q7. How can you view a table's columns?

Answer: Use DESCRIBE table_name; or SHOW COLUMNS FROM table_name;.

Q8. What is a foreign key?

Answer: A foreign key defines a relationship to a key in another table and can enforce referential integrity.

Q9. Can a table contain multiple UNIQUE constraints?

Answer: Yes. A table can contain multiple unique constraints on individual columns or combinations of columns.

Q10. How can you view the complete table definition?

Answer: Run SHOW CREATE TABLE table_name;.

23. Quick Revision Cheat Sheet

24. Conclusion

The CREATE TABLE statement is essential for designing and organizing data in MySQL. A well-designed table uses appropriate data types, meaningful names, primary keys, and suitable constraints to maintain data consistency. Practise creating tables, verifying their structures, and connecting related tables with foreign keys to build a strong foundation in database development.

Continue learning with the JustAcademy MySQL Training Course or register for a MySQL course demo.