How To Create A Table In MySQL

Introduction

Creating tables is a fundamental skill when working with MySQL databases. Tables are used to store and organize data in a structured format, making it easy to query and retrieve the information you need. This article will explore the basics of creating tables in MySQL. We will also discuss how to add constraints to tables, such as primary keys, foreign keys, and check constraints, which ensure the integrity of the data being stored. By the end of this article, you will have a good understanding of how to create tables in MySQL and how to apply various constraints to them.

What is a Table in MySQL?

A table is used to store and organize data in the form of rows and columns. We can also retrieve this table data using a query.

Each row in a MySQL table represents a unique instance of the data being stored, and each column represents an attribute of that data. For example, we have a table called student, for this table, the columns can be student_id, student_name, etc.

We can also add constraints on these tables like primary key, unique key, foreign key, etc., which ensures the integrity of the data being stored. These constraints can reduce redundancy, establish relationships between tables and maintain data integrity.

In MySQL, we can create tables using two ways

Using Command Line
Using Workbench GUI

How to create a table in MySQL?

In MySQL, to create a new table in the database, create table query is used. Now, let's have a look at the syntax of creating tables to have a better understanding of creating tables in MySQL.

Syntax

CREATE TABLE table_name
(  
    column1 datatype,  
    column2 datatype,  
    column3 datatype,  
    ...
);

Here, create is used to create database objects like table, table_name is the name of the table that we want to create, column1, column2, and column3 are the name of the columns of the table we are creating, and datatype denotes the type of data that column stores.

Example

CREATE TABLE student (
   student_id INT,
   student_name VARCHAR(50),
   marks INT,
   percentage FLOAT,
   email VARCHAR(50),
   mobile_number VARCHAR(20),
   age INT
);

In the above example, we have created a table named 'student' having student_id, student_name, marks, percentage, email, mobile_number, and age as columns. We have also specified their data types and size. Running the above query will create a table in our database. And then, we can perform manipulations on this table.

How to Create a Table with Primary Key?

We can create a table with a primary key. The primary key helps us to identify each record uniquely and does not allow any null or duplicate field. To make an attribute primary key, we just write the primary key after the column datatype. We can also make the primary key value auto-increment by adding an auto_increment constraint.

Example

CREATE TABLE student (
   student_id INT PRIMARY KEY Auto_Increment,
   student_name VARCHAR(50),
   marks INT,
   percentage FLOAT,
   email VARCHAR(50),
   mobile_number VARCHAR(20),
   age INT
);

Output

table in mysql

In the above example, we have created a table named 'student' having student_id, student_name, marks, percentage, email, mobile_number, and age as columns. We made student_id the primary key and set auto_increment to its value. We have also specified their data types and size. Running the above query will create a table in our database. And then, we can perform manipulations on this table. And then, we described its structure.

How to create a table with NOT NULL constraint?

The NOT NULL constraint is used to specify that a column must have a value and cannot be NULL. When we create a table in MySQL, we can add the NOT NULL constraint to a column by appending it after the column's data type.

Example

CREATE TABLE demo (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT
);

In the above example, we have created a table named demo, id, name, and age are the columns that we have created. We made the id and name columns NOT NULL to make sure these columns must not be empty.

How to create a table with the CHECK constraint?

In MySQL, the CHECK constraint is not directly supported. However, we can achieve the same functionality by using triggers.

A trigger is a named database object associated with a table and executed automatically when a particular event occurs for the table. We can use a trigger to enforce a check constraint on a table in MySQL.

Example

CREATE TABLE order_table (
  id INT PRIMARY KEY,
  order_date DATE,
  total DECIMAL(10,2),
  status ENUM('pending', 'shipped', 'delivered')
);
CREATE TRIGGER orders_total_check
BEFORE INSERT ON order_table
FOR EACH ROW
BEGIN
  IF NEW.total < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Total must be non-negative';
  END IF;
END;

In the above example, we created a table named order_table with columns id, order_date, total, and status. We want to add a check constraint to ensure that the total column is non-negative. To enforce this constraint, we created a trigger named orders_total_check. The trigger is associated with the orders table and is executed before each insert operation. The trigger code checks whether the value of NEW.total (i.e., the value being inserted into the total column) is less than zero. If it is, the trigger raises an error with a custom message indicating that the total value must be non-negative.

Note that we have used the SIGNAL statement to raise an error in the trigger. This is the MySQL equivalent of the RAISERROR statement in other database systems.

How to create a table with a DEFAULT constraint?

In MySQL, a default constraint is used to specify a default value for a column. This means that if no value is provided while inserting data into the table, the default value will be used instead. We can add a default constraint to a column while creating a table in MySQL.

Example

CREATE TABLE demo (
   column1 datatype DEFAULT default_value,
   column2 datatype DEFAULT default_value,
   column3 datatype DEFAULT default_value,
   ...
);

In the above example, we have created a table named demo which contains three columns, and we have applied the DEFAULT constraint.

Conclusion

MySQL provides a robust and easy-to-use system for creating tables to store and organize data. By understanding the syntax and different constraints that can be applied to tables, you can create tables that are optimized for your specific needs. Whether creating a simple table with basic columns or a complex table with multiple constraints, MySQL offers the flexibility and power to create tables that work for you. With this knowledge, you can start building efficient and effective databases that will help you store, manage, and retrieve your data with ease.