Create Table in MySQL

In MySQL, creating a table is a foundational task for structuring your database. A table consists of rows and columns, where each column represents a specific data type.

In this tutorial, we will demonstrate how to create a table in a MySQL database using mysql Command Line Interface or MySQL Workbench, using the school database as an example.

We’ll start with a basic example and then explore different scenarios for creating a table with specific requirements, such as constraints, default values, and unique indexes.

Prerequisites

Before creating a table, ensure the following:

You have MySQL installed and running.

You are connected to the MySQL server and have sufficient privileges to create tables. The database (in this case, school) exists.

Step 1: Select the Database

First, select the school database where the table will be created. Use the following SQL command:

</>
Copy
USE school;

This ensures that the subsequent operations apply to the school database.

Step 2: Create a Basic Table

To create a table named students, use the following SQL command:

</>
Copy
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    grade VARCHAR(10)
);

This command creates a table with the following columns:

  • id: A unique identifier for each student, automatically incremented.
  • name: The student’s name, which cannot be null.
  • age: The student’s age, which is optional.
  • grade: The student’s grade level (e.g., “10th”, “11th”).

Verify Table Creation

To confirm that the table was created, use the following command:

</>
Copy
SHOW TABLES;

The output should include the students table. To view the table structure, use:

</>
Copy
DESCRIBE students;

Step 3: Creating a Table with Constraints

Constraints enforce rules for data integrity. For example, to create the students table with a unique constraint and a default value:

Note: If you have created students table in the previous step, you may use drop database students; command to delete the table, and create a new one with the constraints using using the following command.

</>
Copy
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 18,
    grade VARCHAR(10) CHECK (grade IN ('10th', '11th', '12th'))
);
  • email: Ensures unique email addresses for students.
  • age: Defaults to 18 if no value is provided.
  • grade: Restricts values to “10th”, “11th”, or “12th”.

Attempting to insert invalid data into the grade column will result in an error.

Example Insert Statement

Insert data into this table using:

</>
Copy
INSERT INTO students (name, email, age, grade)
VALUES ('John Doe', 'john.doe@example.com', 17, '11th');

To view the data, use:

</>
Copy
SELECT * FROM students;

Step 4: Creating a Table with Foreign Key

To establish relationships between tables, use foreign keys. For example, create a classes table and reference it in the students table:

Note: If you have created students table in the previous steps, you may use drop database students; command to delete the table, and create a new one with the constraints using using the following command.

</>
Copy
CREATE TABLE classes (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(50) NOT NULL
);

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

Here, the class_id column in the students table references the class_id column in the classes table. This ensures referential integrity.

Example Insert Statement

Insert data into the related tables:

</>
Copy
INSERT INTO classes (class_name) VALUES ('Math 101');

INSERT INTO students (name, class_id) VALUES ('Alice', 1);

To view the data, use:

</>
Copy
SELECT students.name, classes.class_name
FROM students
JOIN classes ON students.class_id = classes.class_id;

Using MySQL Workbench to CREATE TABLE

MySQL Workbench provides a graphical interface for creating tables in a database, eliminating the need to write SQL commands manually.

In the following steps, we will go through the step-by-step process of creating a table in MySQL Workbench. We’ll use a school database as an example and create a students table with different columns and constraints.

Prerequisite: Ensure MySQL Workbench is installed on your system, and you have a connection to a MySQL server where the database exists or can be created.

Step 1: Connect to the MySQL Server

Launch MySQL Workbench and connect to your MySQL server by clicking on the appropriate connection from the home screen. If you do not have a connection set up, click the + button next to MySQL Connections, fill in the required connection details, and test the connection.

Step 2: Select the Database

Once connected, locate the “Schemas” panel on the left-hand side of MySQL Workbench. This panel displays the databases available on the server. Expand the school database (or create one if it doesn’t exist). If the school database isn’t available, right-click anywhere in the Schemas panel and select Create Schema to create it.

To set the database as active, right-click on the school schema and select Set as Default Schema.

Step 3: Open the Table Creation Tool

In the “Schemas” panel, expand the school schema and locate the Tables folder. Right-click on the Tables folder and select Create Table....

This will open the table creation interface in the main Workbench editor.

Step 4: Define the Table Name

In the table creation interface, provide a name for the new table in the Table Name field. For example, enter students.

Step 5: Add Columns to the Table

Below the Table Name field, use the grid to define the columns for the table. Fill in the following details for each column:

Column Name: Specify the name of the column. For example, id, name, age, and grade. Datatype: Select the datatype for the column, such as INT, VARCHAR(100), or DATE. PK (Primary Key): Check this option for the column that will serve as the primary key (e.g., id). AI (Auto Increment): Enable this option for the primary key column to auto-increment its values. NN (Not Null): Check this option for columns that cannot contain NULL values (e.g., name).

For example, define the following columns:

id (INT, PK, AI, NN), name (VARCHAR(100), NN), age (INT, optional), grade (VARCHAR(10), optional).

Click Finish.

Step 6: Add Constraints or Default Values

To add constraints like unique keys, foreign keys, or default values, use the tabs in the table creation interface:

Indexes: Add unique or composite keys. Foreign Keys: Define relationships with other tables. Default Values: Specify default values for columns in the Default column of the grid.

For example, to set a default value for the grade column, enter 'Unknown' in the Default field.

Step 7: Review and Apply

Once all columns and constraints are defined, click Apply in the bottom-right corner of the interface. MySQL Workbench will generate the SQL script for creating the table.

Review the SQL script to ensure it matches your requirements. For example, the generated SQL might look like this:

Click Apply again to execute the script and create the table.

Click Finish to close the dialog.

Step 8: Verify the Table

After the table is created, verify its existence by expanding the Tables folder under the school schema in the “Schemas” panel. You can also view the table structure by right-clicking the table name and selecting Table Inspector.