How to Create Table in SQL – SQL is used to create and modify database schema. It is also used in creating or editing data records in a database. You can create a table in SQL by using CREATE TABLE statement. It is mainly used to create a tables consisting of multiple column and rows.
Creating tables in databases is a very helpful skill, and not just for software engineers or database administrators. It allows you to design or change the structure of a database and store data that’s related to each other. In this article, you’ll learn what a database table is and how to use the syntax of the CREATE TABLE command.
Table of Contents
What Is a Database Table?
A relational database is built of various structures like tables, views, procedures, and triggers. The main element in a database is the table. It’s a structure that actually stores data. In most cases, a database contains more than one table and the tables are related to each other (i.e. tables store related information, like a person’s name, eye color, and height).
The picture below shows a sample of a database for an online musical equipment shop. (The image comes from the Vertabelo blog; Vertabelo is an online database modeling tool.) Notice that this database has several tables. The lines indicate relationships between tables:
As you see, there are a lot of tables: category, instrument, manufacturer, and more. In a SQL database, every table has a name and stores data in rows and columns. Each row stores details about a specific object.
For example, the table instrument stores information about instruments. Each row is a specific instrument: one row can store information about a white guitar purchased from “Music Shop in Chicago”; another row can store info about a silver drum purchased from “In Tom’s shop”.
The details in each row should be atomic (meaning they can’t get any smaller or more specific), so we have to divide them into columns. Each column stores one kind of data, such as a color, a name, a type of an instrument, or the store in which it was purchased. Below is a sample table with these columns and rows:
| name | color | type | store |
|---|---|---|---|
| guitar | white | chordophone | Music Shop in Chicago |
| trumpet | gold | wind | Music Shop in Chicago |
| drums | silver | percussion | In Tom’s shop |
The table’s name should describe the data stored in that table. You should also define the data type(s) used in the table columns when you create the table. (You need to have a CREATE TABLE permission to do all this, but that’s another article.)
How to Create a Table
Creating a table in a database is very simple. You just need to use the standard SQL syntax for the CREATE TABLE command:
CREATE TABLE table_name( column1 data_type, column2 data_type, … ); |
Let’s dig into what’s going on here. First you put the CREATE TABLE keyword, followed by the table name. This name should not be an SQL keyword (like CREATE, LIKE, or NULL). It must be unique within the database – if one table is named person, other tables in the same database cannot have this name – and describe the data being stored in the table.
After the name, you add a pair of parentheses. Inside the parentheses, you list the names of the columns with their data types. A data type defines what kind of values (numbers, text, dates, times, etc.) you can store in a column. The most popular data types are:
- INTEGER/INT stores whole numbers like -2567, 0 , 1, and 67.
- VARCHAR stores text data. You can specify the maximum number of characters like this: VARCHAR(n). For example,
varchar(4)can store the texts ‘Lisa’, ‘Os’ or ‘Gary’. - CHAR is similar, but instead of setting the max length, you set the absolute length. For example, CHAR(4) will store ‘Os’ as ‘Os ‘. Note the two extra spaces, which make the length up to 4.
- FLOAT stores floating-point numbers, such as -23.789, 23.5, and 78.0.
- DECIMAL is similar to
floatin that it stores decimal-point numbers. However, you can indicate the maximum total number of digits (p) and the number of digits after the decimal (s) like this: DECIMAL(p,s). In other words,decimal(4,2)can store 12.56, 1.56, 70. Unlike FLOAT, DECIMAL offers precise calculations. Use it to store money values. - TIME stores time data, e.g. ‘12:34:50’.
- DATE stores date data, e.g.’2020-09-09’.
- DATETIME stores both date and time data in a single field, e.g. ‘2020-03-30 12:34:50’.
This basic CREATE TABLE syntax is the same for most SQL engines; you can use it in Oracle, SQL Server, MySQL, or PostgreSQL.
Creating a Simple Database Table
Let’s create a table called customer that stores details about a company’s customers. Each row will contain data about one specific customer. The table should have the columns ID, last_name, first_name, and registration_date. The columns defined in the table store atomic data about each customer: their identification number, last name, first name, and the date when they registered as a customer.
Here’s the command we’d use:
CREATE TABLE customer( ID int, last_name varchar(80),first_name varchar(80),registration_date date); |
We start with the CREATE TABLE keyword, after which we give the name of the new table: customer. Next, in the parentheses, we give the column names and data types. The first column is ID, which stores integer values like 1,2,3. The second and third columns, last_name and first_name, are string values; we’ll use varchar(80) for them. Remember, the value in brackets denotes the maximum number of characters in the string. Finally, the column registration_date stores the date when the person was registered.
Data types control what kind of information can be entered in a column; you can’t enter letters or symbols in an INT column like ID.
Defining Columns Without Empty/Repeated Values
The CREATE TABLE statement shown in the last section allows users to insert non-unique values or empty values (NULLs) into a field. For example, you can have the same ID value for more than one customer. We can change this behavior by creating a table with constraints: we’ll add constraints to ID to prevent NULLs and non-unique values; we’ll also add constraints in the columns first_name and last_name to prevent NULLs in these columns. Have a look:
CREATE TABLE customer ( ID int NOT NULL UNIQUE, last_name varchar(80) NOT NULL,first_name varchar(80) NOT NULL,registration_date date); |
SQL offers the NOT NULL keyword, which when added to the column definition doesn’t allow empty values (NULL) when inserting or modifying a row. In our example, we cannot omit (or insert a NULL) into the columns first_name, last_name or ID. They must have a value.
Another valuable keyword is UNIQUE. By adding this keyword, you can make sure that all values in this column are different in every row. In our example, the column ID stores unique values that identify each customer.
Creating a Table from Data Stored in Another Table
We can create a new table without defining columns: the process is based on data and columns in other tables.
Use this method if you want to create tables and insert data stored in specific columns in another table. Here’s the syntax:
CREATE TABLE new_table_name SELECT col1, col2, … FROM existing_table_name ; |
First we provide the CREATE TABLE keyword and the new table name. Next, we use the SELECT command. This allows you to specify the columns and table(s) from which you copy data to the new table. The names and data types of these columns will automatically be defined in the new table.
Below, we create the table history_instrument using the columns ID, name, color, and type from the instrument table:
CREATE TABLE history_instrument SELECT ID, name, type FROM instrument ; |
Notice that the new table doesn’t store all the columns from the table instrument.
Conclusion
There are many types of databases available that you can use with SQL. It is important to understand that relationships have a specific syntax that needs to be followed in SQL, or else they won’t work properly.