How to create a new table with SQL CREATE TABLE

SQL CREATE TABLE is used to create a new table, which must be unique within the database. Important parameters are defined when the table is created, and it’s possible to use an existing table as a basis.

What is SQL CREATE TABLE?

Once you’ve created an SQL database using the Structured Query Language, you can create a new table. This table serves as the repository for your data records and defines the structure in which the data is organized. Tables are composed of fields or columns that hold specific data types and rows where this data is recorded. To create a new table, you use the SQL CREATE TABLE statement. This command not only creates the table but also sets its structure. Afterward, you can fill the table with the relevant data.

Syntax and function

Just by looking at the syntax of SQL CREATE TABLE, you can see that data types and names are mandatory for correctly creating a new table from the start. The syntax is basically as follows:

CREATE TABLE name_of_table ( 
column1 data type, 
column2 data_type, 
column3 data_type,
…
);

Use the SQL command CREATE TABLE to create a new table, specifying its name in the first line. Define the table’s structure within the parentheses. For example, choose names for column1, column2, etc., and specify the data types that can be stored in each column. Common data types include VARCHAR (a string that can contain letters, numbers, or special characters), BOOLEAN (for the Boolean values true or false), INT (for numerical values) or DATE (for dates).

Example of a new table

To illustrate how SQL CREATE TABLE works, we’ll use a simple example to show you what such a table might look like. We’ll create an employee directory that contains an employee ID, surname, first name, address, city, and age. We need a total of six columns to store different data types. The appropriate code looks like this:

CREATE TABLE Employees ( 
ID INT NOT NULL, 
Last name VARCHAR(50), 
First name VARCHAR(50), 
Address VARCHAR(50), 
City VARCHAR(50), 
Age INT NOT NULL
);

For ID and age, we select the data type INT, which contains numbers. The addition NOT NULL specifies that these fields must not be left blank. The remaining columns Last name, First name, Address and City may contain letters, numbers and special characters.

Combination with the IF NOT EXISTS command

Each table must be unique within a database so that the system can access it. If you try to create a new table with SQL CREATE TABLE and choose a name that already exists, you will get an error message like Error: table already exists. To avoid this, you can add the optional command IF NOT EXISTS. This command first checks whether a table with the same name already exists. If it does not, it creates a new table with the specified parameters. For our example above, it looks like this:

CREATE TABLE IF NOT EXISTS employees ( 
ID INT NOT NULL, 
Last name VARCHAR(50), 
First name VARCHAR(50), 
Address VARCHAR(50), 
City VARCHAR(50), 
Age INT NOT NULL
);

Create a new table based on an existing table

Even though the statement is quite simple, it can be tedious to create several similar tables. SQL CREATE TABLE also gives you the option of using an existing table to create a new one. You can choose whether to create an exact copy or only take certain parameters into account. We’ll show you both methods. In the first case, we create an employee list for another branch. This table will be set up identically but requires a new name. Here is the code:

CREATE TABLE New_employee_list
AS
SELECT *
FROM Employees;

The asterisk indicates to the system that it should accept all columns.

In the following example, we create a customer list. This should also contain the columns for ID, surname, first name, address and city, but the date of birth should not be created. We therefore specify which columns are to be copied:

CREATE TABLE Customer_list
AS
SELECT ID, last name, first name, address, city 
FROM Employees;

Similar commands to SQL CREATE TABLE

In addition to the CREATE DATABASE statement already mentioned, there are other important statements for working with SQL CREATE TABLE. These include SQL DELETE to delete entries from a table, and SQL Aliases to temporarily rename tables or columns.