How to Use SQL CREATE TABLE Statement with Examples

SQL is a powerful language for querying and data management in relational databases. One of the most essential commands in SQL is the CREATE TABLE statement, which permits you to create a new table and define its structure, including the columns, data types, and constraints.

In this blog, we will explain the syntax of the SQL CREATE TABLE statement and show you how to utilize it to create effective database structures in MySQL and SQL database.

Connect to SQL Database

As a prerequisite, it is required to connect to the existing database or create a new one if you do not have it already. To do so, connect to the MySQL shell as the root user and enter your password when prompted.

# mysql -u root -p

Create Database in MySQL

For the demonstration purpose, we will create a new database named “database1” by utilizing the CREATE DATABASE command or statement.

CREATE DATABASE database1;

Next, connect to the database by specifying its name in the USE statement as follows.

USE database1;

Now, you are all set to study the SQL CREATE TABLE statement.

1. What is SQL CREATE TABLE Statement?

SQL CREATE TABLE statement is utilized for creating a new table in the current or selected database. It specifies or defines the table name, along with the names and data types of its columns, as well as any constraints that should be applied.

More specifically, the CREATE TABLE statement is essential for creating and organizing data within a database and is a fundamental tool for anyone working with SQL.

CREATE TABLE Syntax

Follow the provided syntax to utilize the CREATE TABLE statement.

CREATE TABLE table_name (
    col1 datatype,
    col2 datatype,
    col3 datatype,
    ....
);

Here:

• The “CREATE TABLE” command is added for creating a new table.
• The “table_name” refers to the new table name.
• The “col1“, “col2“, “col3“, etc are the column names of the table.
• The “datatype” indicates the type of data going to be saved in the mentioned columns.
• The column definitions will be separated by commas "," and must be enclosed in parentheses "()".
• Lastly, end the statement with the semicolon ";".

2. How to USE SQL CREATE TABLE Statement?

Now, we will utilize the CREATE TABLE statement for creating a table named “authors” having three columns “id”, “name”, and “age”, where the first and third columns id and age are of “INT” integer type, the name is of “VARCHAR” variable-length characters type of size “50”.

Add Columns in a Table in MySQL

CREATE TABLE authors(id INT, name VARCHAR(50), age INT);

For the verification of the performed operation, run the SHOW TABLES command.

SHOW TABLES;

It can be observed that the “authors” table has been created in our current database1 Or describe the table to view its complete structure.

DESC authors;

3. Data Types in SQL CREATE TABLE Statement

The SQL CREATE TABLE statement supports several data types and the description related to each of them is enlisted below:

• INT – This data type is utilized for storing integer values (whole numbers).
• VARCHAR(n) – This datatype is used for storing variable-length character strings, where n indicates the maximum length.
• CHAR(n) – This datatype is utilized for storing fixed-length character strings, where n represents the length of the characters.
• FLOAT – This data type is utilized for storing floating-point numbers, specifically numbers having a decimal point.
• DATE – This datatype is used for storing dates, such as year, month, and day.

4. Use Different Data Types in SQL CREATE TABLE Statement?

For instance, we will create a table named “employees” with the following five columns.

CREATE TABLE employees (
  id INT, 
  name VARCHAR(50), 
  department CHAR(3), 
  pay FLOAT, 
  hire_date DATE
);

According to the above-given statement:

• The “employees” is the table name.
• The first column is named “id” having an “INT” integer datatype.
• The second column is named “name” having a “VARCHAR” data type with the variable-length string specified up to “50” characters.
• The third column named “department” has a “CHAR” data type, a fixed-length string of “3” characters.
• The fourth column named “pay” is of “FLOAT” type.
• The fifth column named “hire_date” is of “DATE” type.

5. Constraints in SQL CREATE TABLE Statement

In SQL, constraints refer to the rules that can be applied to single or multiple columns to make sure that the data entered in those columns meet the defined criteria or conditions.

Let’s enlist some common constraints that can be utilized in the SQL CREATE TABLE statement.

• NOT NULL – This constraint verifies that a column does not contain a null value. More specifically, an undefined or unknown value.
• UNIQUE – This constraint verifies that the column values are unique.
• CHECK – This constraint checks that the column meets the specified or defined condition.
• PRIMARY KEY – This constraint identified a unique row in a table. It is a combination of NOT NULL and UNIQUE constraints.
• FOREIGN KEY – This constraint makes sure that the first column values match the values of the other column table.

6. Use Constraints in SQL CREATE TABLE Statement

Have a look at the provided example to know more about using constraints in the SQL CREATE TABLE statement.

CREATE TABLE customers (
  id INT PRIMARY KEY, 
  name VARCHAR(60) NOT NULL, 
  email VARCHAR(60) UNIQUE
);

The above statement will create a table named “customers” having three columns id, name, and email.

According to the above-given statement:

• The id column is specified as the PRIMARY KEY of the customers’ table, which signifies that its values must be unique and not null.
• The name column constraint is defined as NOT NULL so that it cannot be left empty.
• The email column constraint is specified as UNIQUE, which means that there won’t be any duplicated email addresses.

Likewise, we have created another table named “orders” with four columns, “order_id“, “customer_id“, “order_date“, and “total_amount“.

CREATE TABLE orders (
  order_id INT PRIMARY KEY, 
  customer_id INT, 
  order_date DATE, 
  total_amount FLOAT CHECK (total_amount > 0), 
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

According to the given statement:

• The order_id is the PRIMARY KEY of the created table.
• A CHECK constraint is applied to the total_amount column to make sure that the column value must be greater than 0.
• The customer_id is the FOREIGN KEY of the orders table that REFERENCES the id column of the customers’ table, which we have already created. This signifies that each value of the customer_id column should correspond to a valid value of the id column of the customer table.

7. Delete a Table in MySQL

In order to delete a SQL Table, execute the “DROP TABLE” statement and specify the table name.

DROP TABLE orders;

As a result, the orders table will get dropped from the current database1. You can also delete table data or rows using the DELETE statement.

8. Best Practices for Using SQL CREATE TABLE Statement

Check out the provided list of the best practices for utilizing the SQL CREATE TABLE statement:

• Choose descriptive and meaningful columns and table names.
• Consistent naming conventions should be utilized for database objects. For instance, you can use a prefix in order to identify relevant tables or columns that belong to the mentioned system.
• Utilize the appropriate data types of the columns with respect to the data you are going to store in them.
• Apply constraints on the columns for ensuring data integrity, such as checking column values, or setting primary or foreign keys.
• Avoid redundant or unnecessary columns. This will assist in designing a scalable table structure with better performance.

That’s how you can utilize the SQL CREATE TABLE statement in MySQL.

Conclusion

The SQL CREATE TABLE statement is a robust tool for creating database tables with suitable structures. Using this statement, you can define data types for the columns and add constraints. In this way, it is easier to maintain data integrity and optimize overall performance.

Moreover, by following the best practices and using this statement effectively, you can create tables or insert data into a table that meet your database requirements and help your business grow.

Follow GeeksVeda to keep up-to-date related to new content in this SQL Series!